7

I recently came across this strange behaviour in Postgres. I had a table like the following one:

sasdb=# \d emp_manager_rel 
           Table "db3004db.emp_manager_rel"
   Column   |  Type  | Collation | Nullable | Default 
------------+--------+-----------+----------+---------
 emp_id     | bigint |           |          | 
 manager_id | bigint |           |          | 

select * from emp_manager_rel ;
 emp_id | manager_id 
--------+------------
      1 |        123

I executed the following update statement :

UPDATE 1:

update emp_manager_rel set manager_id = manager_id+emp_id , emp_id=emp_id*4;

which update the table like follow:

 emp_id | manager_id 
--------+------------
      4 |        124

UPDATE 2: I executed the following query (on the original table, not on the updated)

update emp_manager_rel set  emp_id=emp_id*4 , manager_id = manager_id+emp_id ;

it updates the table like follows:

 emp_id | manager_id 
--------+------------
      4 |        124

I am expecting the value of manager_id on UPDATE 2 to be 127 (because emp_id has been changed to 4 by empid * 4). But, both UPDATES produce the same result. I wonder what will be the order of execution of set clause in ANSI standard.

Improve this question

1 Answer 1

Reset to default
12

There is nothing "strange" about this behaviour - it's the only sane way to process such an update.

There is no such thing as "order of updates" - all SET clauses should be assumed to happen in parallel.

The SQL standard requires that the right hand side of the assignment is the value of the columns as it was before the UPDATE statement started. The order in which the column assignments are listed in the UPDATE statement is irrelevant for the outcome of the UPDATE statement.

Any other behaviour of the UPDATE statement would be a bug and a violation of the SQL standard.

For the same reason, the following statement will swap the two column values:

update some_table
   set x = y, y = x;

or 

update some_table
   set y = x, x = y;
Improve this answer
4
  • this is regarding alter statements, is there anything like "order" in executing alter statement. We have alter statements like drop col1,drop col2,add col1 in same stament. I am working on a project which involves creating DB like layer in java. So I need how SQL behaves in this scenario and have to pretend the same. Any thoughts regarding alter execution Commented Aug 27, 2018 at 16:17
  • 2
    @DineshKumar SQL statements are designed to be atomic. Just make multiple statements. Commented Aug 27, 2018 at 17:35
  • 1
    @DineshKumar: I have no idea what you mean with the "order" when executing ALTER statements. You should ask a new question. But remember: DDL is transactional in Postgres, even if you use multiple statements you can make them appear as a single one to other sessions if you put everything into a single transaction. Commented Aug 27, 2018 at 21:33
  • @a_horse_with_no_name sure will post a new question with better use case reg. this scenario. Commented Aug 28, 2018 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.