Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant performance gain? Are there other benefits to updating a set of records in this way?

For example,

UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo';

vs

UPDATE table1 SET value1='foo' WHERE value2='bar';

A more concise example:

+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| Field              | Type                                            | Null | Key | Default | Extra          |
+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| pk_id              | int(10) unsigned                                | NO   | PRI | NULL    | auto_increment | 
| user_id            | int(11)                                         | YES  |     | NULL    |                | 
| status             | enum('unread','accepted','deleted')             | YES  |     | NULL    |                | 
| processed_datetime | datetime                                        | YES  |     | NULL    |                | 
| some_column        | varchar(255)                                    | YES  |     | NULL    |                | 
+--------------------+-------------------------------------------------+------+-----+---------+----------------+

...with another example query:

UPDATE table1 SET status = "accepted", processed_datetime = NOW() WHERE pk_id = 1234 AND user_id = 5678 AND status != "accepted"

(Note: this is not my code / schema, someone else's that I am working with)

share|improve this question
    
Show you provide us with an table structure? –  Raymond Nijland Nov 13 '13 at 22:59

2 Answers 2

This very much depends on the data present in the table at the time of the UPDATE.

If, for instance, the table has 1,000,000 rows where value1 = 'foo' and only 1 row where value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; will only actually update a single row. Whereas UPDATE table1 SET value1='foo' WHERE value2='bar'; would update 1,000,001 rows.

If you are talking about a table with only two rows, the difference is entirely negligible.

If you were using SQL Server and had a filtered index like WHERE value2 = 'bar' AND value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; would most likely use the index instead of scanning the entire table.

share|improve this answer
    
In your second example, would it actually update 1,000,001 rows? <code>Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0</code> Wouldn't you see a result like, Rows matched: 1,000,001 Changed: 1 ? –  Todd Nov 13 '13 at 21:04
    
I would estimate the engine will change all rows to the new value. It doesn't waste time inspecting the value to see if it already matches since you haven't told it to do that. –  Max Vernon Nov 13 '13 at 21:17

In my opinion, having extra condition in WHERE makes no difference in queries like UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo'; .

However, UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 = 'bar1'; is different. First of all, if you have an index on bar1 it will be used because UPDATE has read component and it has to locate record first. Secondly, it may be used to prevent 'lost update' problem to some extent, it looks to me like a sort of optimistic locking.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.