CODEWITHSUNDEEP
mysql
Eric Sietsema
Eric Sietsema

Reputation: 31

Mysql Copy And Update In Same Statement

I know this works, but is it good practice?

update test set field1=field1+field2, field2=0;

I don't want field2 to be set to 0 until the value has been added to the field1 total. Will this always work as planned?

Upvotes: 0

Views: 75

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

It should work if MySQL followed the standard. But it doesn't, (follow the standard) in this case. Note. In standard ISO?ANSI SQL, these statements are equivalent and produce the same changes in the table. (Anyone can try them in SQL-Server or Oracle. The 3rd variation works in Postgres only I think):

UPDATE test 
SET field1 = field1 + field2, field2 = 0 ;

UPDATE test 
SET field2 = 0, field1 = field1 + field2 ;

UPDATE test 
SET (field1, field2) = (field1 + field2, 0) ;

So, in MySQL your statement will work like you expect most of the times, as the MySQL documentation states:

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

Note the "generally" though. To be 100% sure, you can do the update in 2 statements (inside a transaction):

UPDATE test 
SET field1 = field1 + field2 ;

UPDATE test 
SET field2 = 0 ;

or using a temporary table.

Upvotes: 2

x4rf41
x4rf41

Reputation: 5337

found it in the manual (http://dev.mysql.com/doc/refman/5.0/en/update.html):

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

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

so it will always work

Upvotes: 0

Related Questions