0101 0101 - 1 year ago 62
SQL Question

How to update value in MySQL when the stored version differs to the passed argument

UPDATE table SET x = :x, y = :y, z = IF(x <> :x, NOW(), z);

I need to check whether value of the old
is different from the new
. I can't do that within the
statement as shown above because value of
is already changed so it equals to the

Basically, what I am trying to do is something like:


Is there any way how to achieve that or am I better doing this using a subquery or a variable? *I definitely prefer executing one query only.

Answer Source

MySQL does not support the standard on this:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

So, the following works in a simple example in SQL Fiddle:

UPDATE table
    SET x = if (@x := x, :x, :x),
        y = :y,
        z = IF(x <> @x, NOW(), z);

It should also work if you reverse the order:

UPDATE table
    SET  z = IF(x <> :x, NOW(), z),
         x = :x,
         y = :y;

The documentation says that single table updates are "generally" processed in lexical order.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download