MySQL: how to update column using value before change

Question

There is a table with three column: id, field1, field2.

And there is a row: id=1, field1=1, field2=1.

Run a update SQL: UPDATE my_table SET field1=field2+1, field2=field1+1 WHERE id=1;

I expected the result is: id=1, field1=2, field2=2. But in fact I got: id=1, field1=2, field2=3. Because when calculating field2=field1+1, the value of field1 has changed!

I figure out a SQL to solve this problem:

UPDATE my_table dest, (SELECT * FROM my_table) src
SET dest.field1=src.field2+1, dest.field2=src.field1+1
WHERE dest.id=1;

However I want to insert a record, and if the row was existed then do a update just like above.

INSERT INTO my_table (id, field1, field2) VALUES(1, 1, 1)
ON DUPLICATE KEY UPDATE
    field1=field2+1, field2=field1+1;

This SQL has problem same as the first SQL. So how can I do this update using the value before change with ON DUPLICATE KEY UPDATE clause?

Thanks for any help!

LIvanov · Accepted Answer · 2017-05-25 19:04:18Z

1

Couldn't think of anything else but a temp variable. However, couldn't think of a way to make SQL syntax work, other than this:

set @temp = 0;
update test.test set 
    f1 = (@temp:=f1), 
    f1 = f2 + 1, 
    f2 = @temp + 1 
where id = 1;

Hope this helps, and hope even more it helps you find a better way :)

answered May 25, 2017 at 19:04

LIvanov

1,3482 gold badges14 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dnoeth Over a year ago

Btw, directly swapping two columns using SET a= b, b=a is working in every other DBMS, only mysql doesn't follow Standard SQL and skews it up :(

someblue Over a year ago

Temp variable is a good idea! But What's the syntax to use temp variable in INSERT ... ON DUPLICATE KEY UPDATE ...?

someblue · Accepted Answer · 2017-05-26 05:41:06Z

0

I find a trick way to do this.

Use the IF clause to create temp variable. Field update use temp variable to calculate.

INSERT INTO my_table (id, f1, f2) VALUES(1, 1, 1)
ON DUPLICATE KEY UPDATE
    id=IF((@t1:=f1 & @t2:=f2), 1, 1), f1=@t2+1, f2=@t1+1;

There is some point to notice:

The performance is a bit slow. Especially copy TEXT value to temp variable.
If field id need to use IF clause, the expr will be more complicated like:
```
((@t1:=f1 & @t2:=f2) || TRUE) AND (Your Condition)
```

answered May 26, 2017 at 5:41

someblue

4951 gold badge6 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL: how to update column using value before change

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related