Data Manipulation

To manually insert or update data in a MySQL Table, right click on the table in the Object Browser and select the Open Table or press F11. It is populated by a simple SELECT query generated by SQLyog itself. This tab will always display the table data in 'paged mode' and above the GRID you will find controls (like arrow keys) to navigate through the complete table.

The specified number of rows as shown in the Limit box is displayed. To see a limited number of rows, specify the limits in the edit boxes and press Refresh. To see all rows check All rows option and press Refresh. The value you entered will now be stored by SQLyog and the value will have effect for that particular table in that particular database from now on - also after a program restart. Observe that a table is not identified by connection details so if you have same 'databasename.tablename' table on different servers (for instance replicated or synchronized servers) SQLyog data tab will display the same number of rows for both tables. Finally, note that only the number of rows defined is stored and thus persistent - the 'offset' value is not - so when viewing table contents next time after display contents of another table, display will always start at first row.

  • To Alter value in any cell, place the cursor at the desired cell and type the new data.
  • To Insert a row at the specified position, enter the data at the last blank row.
  • Multiple selection in the GRID is possible with Shift+Click.
  • To Delete specified rows, check the checkbox against the rows and Click delete button in the toolbar (you may mark several rows for deletion and delete them at a stroke).
  • To sort data just click on the grid header.
  • To set 'NULL', 'EMPTY' (string) or 'DEFAULT' you can right click a cell and choose the option.
  • Also you can enter functions like now() (they must start on first position) or certain KEYWORDS like CURRENT_TIMESTAMP, CURRENT_USER and NULL and they will be stored not as the literal string 'CURRENT_USER' but as what is the meaning of that keyword (like 'memyself'@'somehost'). To store a function or keyword as a literal string (i.e. the string "now()") you can use backticks/backquotes (like `now()`).

When data are updated in the GRID or FORM view saving to the database only takes place when clicking the save icon or when moving cursor to another row (in GRID) or displaying a new row (in FORM). If the data is not saved then there is a warning link at the bottom right cornor (Data modified but not saved) by clicking on that link you may see prepared SQL statement.

Note that it is not possible to perform INSERTs or UPDATEs on spatial data from the DATA and RESULT tabs. You will need to execute INSERT or UPDATE statements from the editor. However a result set of a table not having any spatial data can be UPDATEd also if columns of the table (not shown in the result set) use spatial datatypes.

And also, UPDATE a row of data from DATA or RESULT tab if any of the columns displayed for the actual row contains data identified as binary data by containing a NULL byte ('\0' in C-notation) no matter whether this occurs for binary datatypes (binary, varbinary, BLOB) or string types (char, varchar, TEXT). Instead an error message will print (reason for this: the UPDATE operation would store what was displayed - not the underlying binary data causing the display).

You see the table data in Text with each column separated by a Tab. Viewing table data in text mode is useful to cut and paste plain text into another application.

Toggle between Grid Mode and Text Mode by pressing Ctrl+L