0

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?

Alex
  • 10,470
  • 8
  • 40
  • 62
Eric Sietsema
  • 31
  • 6
  • I think there's no reason for it not to work, since the `set` portion of the statement is evaluated from left to right... but just to be sure, I would split this in two `update` statements and put them in a transaction if possible (commiting it only after both updates are succesful) – Barranka Apr 30 '13 at 15:20
  • That'll work as expected - assuming I'm reading it right that is. You might want to add `WHERE field2 != 0` so that it doesn't wipe any previously modified entries. – CBusBus Apr 30 '13 at 15:20

2 Answers2

2

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.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • @eggyal: No, following the standard, the previous value of `field2` will be used for the `field1+field2` calculation and only then, the `0` will be applied. – ypercubeᵀᴹ Apr 30 '13 at 15:36
0

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

x4rf41
  • 5,184
  • 2
  • 22
  • 33