Order of Execution of Set clause in Update Query (Postgres)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
3
down vote

favorite

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.

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

asked Aug 27 at 13:54

Dinesh Kumar

729

add a commentÂ |Â

up vote
3
down vote

favorite

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

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

asked Aug 27 at 13:54

Dinesh Kumar

729

add a commentÂ |Â

up vote
3
down vote

favorite

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

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

asked Aug 27 at 13:54

Dinesh Kumar

729

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

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

asked Aug 27 at 13:54

Dinesh Kumar

729

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

edited Aug 27 at 14:50

a_horse_with_no_name

35.8k768107

asked Aug 27 at 13:54

Dinesh Kumar

729

asked Aug 27 at 13:54

Dinesh Kumar

729

asked Aug 27 at 13:54

Dinesh Kumar

729

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
7
down vote

accepted

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;

update some_table
 set y = x, x = y;

edited Aug 27 at 15:02

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

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
â€“Â Dinesh Kumar
Aug 27 at 16:17

2

@DineshKumar SQL statements are designed to be atomic. Just make multiple statements.
â€“Â Brandon
Aug 27 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.
â€“Â a_horse_with_no_name
Aug 27 at 21:33

@a_horse_with_no_name sure will post a new question with better use case reg. this scenario.
â€“Â Dinesh Kumar
Aug 28 at 0:58

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215964%2forder-of-execution-of-set-clause-in-update-query-postgres%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
7
down vote

accepted

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.

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;

update some_table
 set y = x, x = y;

edited Aug 27 at 15:02

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

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
â€“Â Dinesh Kumar
Aug 27 at 16:17

2

@DineshKumar SQL statements are designed to be atomic. Just make multiple statements.
â€“Â Brandon
Aug 27 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.
â€“Â a_horse_with_no_name
Aug 27 at 21:33

@a_horse_with_no_name sure will post a new question with better use case reg. this scenario.
â€“Â Dinesh Kumar
Aug 28 at 0:58

add a commentÂ |Â

up vote
7
down vote

accepted

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.

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;

update some_table
 set y = x, x = y;

edited Aug 27 at 15:02

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

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
â€“Â Dinesh Kumar
Aug 27 at 16:17

2

@DineshKumar SQL statements are designed to be atomic. Just make multiple statements.
â€“Â Brandon
Aug 27 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.
â€“Â a_horse_with_no_name
Aug 27 at 21:33

@a_horse_with_no_name sure will post a new question with better use case reg. this scenario.
â€“Â Dinesh Kumar
Aug 28 at 0:58

add a commentÂ |Â

up vote
7
down vote

accepted

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.

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;

update some_table
 set y = x, x = y;

edited Aug 27 at 15:02

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

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.

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;

update some_table
 set y = x, x = y;

edited Aug 27 at 15:02

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

edited Aug 27 at 15:02

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

answered Aug 27 at 14:52

a_horse_with_no_name

35.8k768107

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
â€“Â Dinesh Kumar
Aug 27 at 16:17

2

@DineshKumar SQL statements are designed to be atomic. Just make multiple statements.
â€“Â Brandon
Aug 27 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.
â€“Â a_horse_with_no_name
Aug 27 at 21:33

@a_horse_with_no_name sure will post a new question with better use case reg. this scenario.
â€“Â Dinesh Kumar
Aug 28 at 0:58

add a commentÂ |Â

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
â€“Â Dinesh Kumar
Aug 27 at 16:17

2

@DineshKumar SQL statements are designed to be atomic. Just make multiple statements.
â€“Â Brandon
Aug 27 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.
â€“Â a_horse_with_no_name
Aug 27 at 21:33

@a_horse_with_no_name sure will post a new question with better use case reg. this scenario.
â€“Â Dinesh Kumar
Aug 28 at 0:58

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
â€“Â Dinesh Kumar
Aug 27 at 16:17

@DineshKumar SQL statements are designed to be atomic. Just make multiple statements.
â€“Â Brandon
Aug 27 at 17:35

@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.
â€“Â a_horse_with_no_name
Aug 27 at 21:33

@a_horse_with_no_name sure will post a new question with better use case reg. this scenario.
â€“Â Dinesh Kumar
Aug 28 at 0:58

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky