ID #1055

I only changed one row of data, but SQLyog wants to update more rows in the database. Why?

 

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 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.

 

Tags: -

Related entries:

You can comment this FAQ

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