Identical UPDATE?

Ikviens · #1 (**permalink**) 10-31-11, 06:59

Hello forum,

I am just wondering what a DB reacts (or should react) to an UPDATE statement that includes an identical update. By this I mean updating a column with exactly the same value as what is currently found.

Code:

UPDATE SomeTable
   SET col_1 = col_1

Does the database actually update the column with the self-same new value or is it clever enough to skip execution, "Oh, the new value is the same as the old one. No need to start an update for this row. Next!"?

r937 · #2 (**permalink**) 10-31-11, 07:37

mysql is smart enough, not sure about other database systems

not sure about ANSI SQL either (the forum you posted in)

Ikviens · #3 (**permalink**) 10-31-11, 08:18

Thanks rudy. I was actually thinking of branching update values using CASE:

Code:

UPDATE SomeTable
   SET col_1 = CASE WHEN <cond 1>
                    THEN  col_1 * 10 
                    WHEN <cond 2>
                    THEN  col_1 * 5 
               ELSE col_1 END

I wondered if writing "ELSE col_1" makes the execution considerably slow compared to a statement that has "<cond 1> OR <cond 2>" in a WHERE clause. Glad to know that I don't have to worry about it for MySQL. Still, I will wait for info about "da standard" and other DB products.

By the way, what if the ELSE condition were "col_1 * 1"? Maybe SQL is not very good at processing equations?

JarlH · #4 (**permalink**) 10-31-11, 09:15

What about transaction isolation levels and write-sets?

Example:
You have a table with one million rows, where 10 rows fulfill <cond 1>
and 10 other rows fulfill <cond 2>.

If the UPDATE statement has no where clause the transaction involves all 1 000 000 rows. What do you want to happen if somebody else at the same time updates one of the 999 980 "else" rows?

Ikviens · #5 (**permalink**) 10-31-11, 09:51

I have a more rudimentary case where the data size is under 100 and the change is more or less permanent, but I see your point. THanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech