SQLyog FAQ

Let us explain you the situation that can result in such behavior.

If your table has a primary key, SQLyog uses the value of the primary key to construct the WHERE clause for UPDATE.

If your table that does not have a primary key then SQLyog uses data from ALL columns of the edited row to construct the WHERE clause. This is done to minimize the chances of duplicate updates. However, if 2 or more rows contain exactly the same data, there is no way for SQLyog ( or any SQL client ) to issue a SQL statement that will update one and only one row.

Let us take the example of a table that has the following structure ( no primary keys ) and 2 rows:

Now if I edit the first column in the first row of data the UPDATE SQL command would be:

UPDATE set code= WHERE CODE=’YOG’ AND DESC=’SQLYOG’

And this will update BOTH ROWS even if your intent was to update the first row only.

To avoid this happening your tables must have a Primary Key or there must not be duplicate data in the table (or the subset of the table that was SELECTed into the SQLyog RESULT-pane, if you are working from the RESULT-pane).

You should also be aware that SQLyog most often does not include (TINY/MEDIUM/LONG) TEXTs and BLOBS in the WHERE-condition that it generates. So with a table definition without a PK and where only the content of such TEXT/BLOBs differ between rows you can get this message too. We recommend that you use a PK and CHAR/VARCHAR/VARBINARY types and not TEXT/BLOB types whenever possible. In particular note that a LONGTEXT/LONGBLOB can be as big as 4 GB (GigaByte). Using such long data in a WHERE-condition is not practicable of course.

The two situations where (TINY/MEDIUM/LONG) TEXTs and BLOBs are included in the WHERE-condition are

• TEXT/BLOBs that are primary keys
• WHEN there is a single column in a table only and this is TEXT/BLOB

If you have a table with several TEXT/BLOBs and no other column types we recommend that you add an autoincrement INTEGER column to the table.  This INTEGER does not need to be a PK so you can still use a TEXT/BLOB field as a PK if you need.