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:CODE | DESC |
YOG | SQLYOG |
YOG | SQLYOG |
Now if I edit the first column in the first row of data the UPDATE SQL command would be:
UPDATE
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.
Comment of Matt T:
The answer is inaccurate: ...limit 1 will cause your statement's scope to only affect the first row. This is an option if the user is unwittingly working with a table w/o a key.
Added at: 2006-12-01 04:19