SQL clause to optimize UPDATE queries?

Question

In my fictional database, I have several columns of data. In designing a PHP front-end to the script, it was necessary to allow the user to modify all attributes of a tuple if necessary.

If the end-user only ends up modifying one attribute instead of all of them, the following statement:

UPDATE foo 
   SET name='bar' location='YYZ' drink='ale' 
 where user='smithj'`

Would update all three attributes of the tuple "smithj", even if two of the attributes are the same.

Is there a way to have SQL (MySQL, if it makes a difference) automatically filter out the redundant updates, similar to the IF EXISTS clause in CREATE TABLE IF EXISTS?

Thanks!

Answer 1

You need to do this in your code. In any case, the "example" query you provide cries for SQL injection :)

Use parametrized queries, build them on the fly, and add only the items you want to update.

Answer 2

Personally you are most likely trying to do a pre-mature optimization at this point. Updating the three values to make them what they need to be is going to be just fine.

Checking to see what to update will most likely cost more to do.

Answer 3

The first question to answer would be why?

The only reason I see is performance. But performance challenges might come for two reasons:

• moving data between your application and the database. You can reduce that by only including the columns in the update statement that actually need updating. But that is a lot of work and probably not worth it when we are talking about three columns. Note also that the large amount of different sql statement this creates might kill some database internal caching. I don't know if this is true for MySQL, but it is for oracle. (assuming you use bind variables (which you should))

• and actually manipulating data in the database. But at that stage, finding the record to update (the where clause) is much more costly then actually updating it. And I guess that is the reason why there isn't any feature, that turns the update of, when old and new values are actually the same

Answer 4

Going along with what Sunny suggested, you have to parametrize your query.

One option could be this (in high-level pseudocode): When a user edits an item (lets say on keyPress), you can flag that cell. You can get the value from every flagged cell, and only pass their values into your parametrized query.

However, this being sad, unless you have a huge number of rows, it shouldn't make that much of a difference to your performance.

Answer 5

"If you set a column to the value it currently has, MySQL notices this and does not update it."

http://dev.mysql.com/doc/refman/5.0/en/update.html