Update mysql if another field is updated

Question

I need to update a field based on the value of another field, updated in the same query.

For example, this is may be my code:

UPDATE my_table SET qty = qty+1, complete = IF(qty = '10', '1', '0') WHERE id = '1'

Value of qty in the IF condition is the value before or after the update?

Do you really want to solve this in the same `UPDATE` query? Have you had a look at triggers? — Nico Haase, Feb 12 '19 at 08:54

score 0 · Answer 1 · answered Feb 12 '19 at 08:34

0

UPDATE my_table SET qty = qty+1, complete = IF(qty = 9, '1', '0') WHERE id = '1'

The value in condition should be compared to value before the update, so if your condition should compare to 10 after update, so you should compare against 9 in the statement (if you increment value in comparison by one :) ).

Also, you comparing (what I guess is) numerical data to string value (in apostrophes), which causes implicit casts. You should be aware of that :)

answered Feb 12 '19 at 08:34

Michał Turczyn

32,028
14
47
69

Yes, but this is valid if the value will always increase. But the field "qty" could increase or decrease. i could have qty = qty+4 or qty = qty-2 The number is a variable. – Vardar Feb 12 '19 at 08:37
@Vardar Then store the value in variable and in condition write `qty = 10 + @variable`, and in update statement `qty = qty + @variable` . That should work – Michał Turczyn Feb 12 '19 at 08:52

score 0 · Answer 2 · answered Feb 12 '19 at 08:42

0

The value is after update of qty by "qty = qty+1" So if you have qty = 5 in the table.

qty in statement "IF(qty = '10', '1', '0')", will be 6.

answered Feb 12 '19 at 08:42

Jatinder Kumar

503
6
17

Update mysql if another field is updated

2 Answers2