Dangerous Multi-Row Table Updates In Results Window

[aramando]

I've just been using the SQLyog results window to make direct changes to the underlying tables, by selecting a table from the drop-down to enable editing of one of its fields, and then clicking save.

I found that in some cases, changing a single field of a single row will cause a warning that multiple rows will be updated. I thought this was strange, and went looking at the underlying query SQLyog is performing.

I was alarmed to find out that, in some cases, the query is as simple and unconstrained as:

UPDATE table SET field = value2 WHERE field = value1

However, I found in other cases one or more additional WHERE conditions were included to ensure only the desired row was updated, although in no case was the primary key used.

I realise SQLyog warns the user when multiple rows are to be updated, but surely when fields are being edited in this way the user would only ever want to edit a single row at a time, and it seems very strange that the primary key of the table being updated is not employed to ensure that this is what happens.

SQLyog Community v11.0 Beta1 64bit

[Jan.S]

Sorry, discard my previous reply. I did not read that you were updating from the results window.

While updating from the results window, only fields from a table which are present in the result set, are used for updating.

Lets say you have a table with primary key 'id' and another field 'data'.

If your result set has only 'data' in it, then it will update the table with the query

UPDATE table SET field = value2 WHERE field = value1

as other fields in the table are not available.

If the table's primary key is part of the result set, then it will use the primary key in the where clause.

[aramando]

Ah, right, thanks. That does make sense.

Although I am happier about this way of operation now that I understand it, and can envisage scenarios where this could be useful, I still think it would be better if SQLyog always included the primary key in the underlying query when editing in the result window. I'm sure that wouldn't be too difficult for SQLyog to do, and it makes sense that when you are editing a single cell of the result table you can only change a single row's field.

[Author]

Posts