create table test (
id serial primary key,
insert into test (c1,c2) values (1,1);
-- here (id, c1, c2) = (1, 1, 1)
update test set c1=2, c2=c1+2;
SELECT * FROM test ;
Postgres is right. According to the MySQL documentation:
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;
I don't think you should ask for Postgres to be broken.
The logic that Postgres (and ANSI) use is simple. In an update statement, the values on the left of the
SET refer to the new record; the values on the right refer to the old record. The ordering of the assignments is not important.
MySQL has different rules.
In Postgres, you could do something like this:
update test set c1 = t1.new_c1, c2 = new_c1 + 2 from (select t1.*, 2 as new_c1 from test t1 ) t1 where test.id = t1.id;
Obviously, for a simple constant this is overkill, but for a complex expression it might make sense. Also, because the
JOIN is on primary keys, there is little impact on performance.