4

Very simple question yet I could not find the answer.

Suppose I have table var with columns foo and bar, both of which are integers and currently at 0.

If I run:

UPDATE var SET foo=foo+1, bar=foo+1

Then what will be the value of bar? 1 or 2?

Does it change if I switch the order?

UPDATE var SET bar=foo+1, foo=foo+1

kmoney12
  • 4,413
  • 5
  • 37
  • 59
  • There is no incremental statement, therefore if foo = 0 then you will get 1, 1 in both columns. – BaconSah Feb 20 '13 at 20:13
  • What does an incremental statement look like? (if I want `bar` to be 2) – kmoney12 Feb 20 '13 at 20:13
  • You would have to use a loop Do until set val = val + 1 end until as a pseudo statement, i.e. in a stored procedure or in the main program. And yes, I agree this is a dupe. – BaconSah Feb 20 '13 at 20:18
  • 3
    I'm going to agree with the possible duplicate - but I'd be curious also why sqlfiddle seems to disagree. http://sqlfiddle.com/#!2/ffdb6/1 Something sqlfiddle-specific? – Joe Feb 20 '13 at 20:18
  • 1
    @joe maybe not a dupe then. The dupe is SQL Server and standard SQL. Maybe MYSQL behaves differently. I'll vote to reopen if closed as that is not the behaviour described in the possible dupe. – Martin Smith Feb 20 '13 at 20:20
  • 1
    And from here looks as though it definitely is different http://stackoverflow.com/a/559291/73226 – Martin Smith Feb 20 '13 at 20:26

4 Answers4

7

As indicated by Joe's SQL Fiddle comment, it does indeed matter what order the column updates are performed. The MySQL documentation for UPDATE indicates this, stating that the expression for each column update will be evaluated in the order declared, and it always uses the "current" value of a column:

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

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;

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 for UPDATE var SET foo=foo+1, bar=foo+1, foo will be set to foo+1, then the bar update will use the newly set value of foo in foo+1.

It may also be worthwhile to note that this behavior differs from the SQL standard, which indicates that all column updates should conceptually happen "at the same time".

Community
  • 1
  • 1
matts
  • 6,738
  • 1
  • 33
  • 50
2

The order does matter in MySQL. Moreover, you can do the following:

update test set foo=foo+1, bar=foo+1, foo=bar+1;

BTW, here is another useful MySQL-specific trick:

insert into test (foo, bar) values ('some text', MD5(foo));
newtover
  • 31,286
  • 11
  • 84
  • 89
0

take note - for postgresql the query executes with values at the start of the query, not the dynamically changing values while the query is executing

create table test (
field1 int2 default 0,
field2 int2 default 0
);


insert into test values (1, 2);


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


select * from test;

Results:

field1, field2
    2,2
hackg
  • 95
  • 1
  • 6
-2

foo=1, bar=1 regardless of order [edit: in MS-SQL. (apparently my bad in MySQL)]

bwperrin
  • 680
  • 5
  • 12