anonymous anonymous - 28 days ago 7
MySQL Question

Update a table column, then the other column with updated value of the former. MySQL / PostgreSQL differ

create table test (
id serial primary key,
c1 int,
c2 int
);

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 ;


MySQL gives (1,2,4), Postgres gives (1,2,3).

I would like PostgreSQL to behave like MySQL, to work with updated value and not original one. Is it possible? Otherwise, if I have indexes on the column and do two updates instead of one, then I have unnecessary UPDATE = DELETE + INSERT (for PostgreSQL), and double the work. In particular case, I actually have to do 4 updates instead of one :(. Doing addition is easy outside SQL, but I have SQL related function instead.

N.B. As far as I remember, UPDATE = DELETE + INSERT may not be the case for numeric types, or is it if they have indexes? For varchars it is always true.

Answer

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.