MySQL: update a field only if condition is met

Question

Is it possible to do an UPDATE query in MySQL which updates field value only if certain condition is met? Something like this:

UPDATE test
SET
    CASE
        WHEN true
        THEN field = 1
    END
WHERE id = 123

In other words:

UPDATE test
SET
    something = 1,        /*field that always gets updated*/
    CASE
        WHEN true
        THEN field = 1    /*field that should only get updated when condition is met*/
    END
WHERE id = 123

What is the proper way to do this?

The only reason to use case in the update query is if you want to update the rows differently depending on a condition. If you just want to update some rows, just add the conditions to the where clause. — SilverSnake, Feb 15 '13 at 11:12

score 163 · Accepted Answer · edited May 29 '17 at 09:30

163

Yes!

Here you have another example:

UPDATE prices
SET final_price= CASE
   WHEN currency=1 THEN 0.81*final_price
   ELSE final_price
END

This works because MySQL doesn't update the row, if there is no change, as mentioned in docs:

If you set a column to the value it currently has, MySQL notices this and does not update it.

edited May 29 '17 at 09:30

radoh

4,554
5
30
45

answered Feb 15 '13 at 11:10

fedorqui

275,237
103
548
598

8

"field" would be only updated if condition is met, **otherwise nothing is done.** – Hamlet Hakobyan Feb 15 '13 at 11:11
Yes! Exactly what I want it to do. – fedorqui Feb 15 '13 at 11:12
3

What about `WHERE currency=1`? – Hamlet Hakobyan Feb 15 '13 at 11:13
It is also a good way. In fact I did not post the whole command I am using, that involves the update of other fields. Just wanted to post this to show the use of `WHEN` in an `UPDATE` query. – fedorqui Feb 15 '13 at 11:16
4

@fedorqui As your query looks like right now you will update the rows where current != 1 as well, to the same value as it already has. This could fire update triggers and have other effects like updating "ON UPDATE" `TIMESTAMP` columns. – SilverSnake Feb 15 '13 at 11:16
Looks like this option works best for me, because it would still update other fields in the query. – Caballero Feb 15 '13 at 11:25
2

@fedorqui, I took the liberty of updating your answer with a documentation reference of why this actually works. – radoh May 29 '17 at 09:31
@radoh and I thank you very much for such improvement! This is in fact good to know for the case of triggers that [someone mentioned](https://stackoverflow.com/questions/14893355/mysql-update-a-field-only-if-condition-is-met/14893412?noredirect=1#comment20886578_14893412) a while ago. – fedorqui May 29 '17 at 09:34
I tried this without the `ELSE final_price`. This caused the rows where `currency!=1` to become empty. The original values, whatever they may be, were erased. Hope this is helpful. – puifais Nov 20 '17 at 23:57

Dominique · Answer 2 · 2019-10-31T07:47:23.273

50

Another solution which, in my opinion, is easier to read would be:

UPDATE test 
    SET something = 1, field = IF(condition is true, 1, field) 
    WHERE id = 123

What this does is set 'field' to 1 (like OP used as example) if the condition is met and use the current value of 'field' if not met. Using the previous value is the same as not changing, so there you go.

edited Oct 31 '19 at 07:47

answered Jan 29 '16 at 10:45

Dominique

1,080
14
29

1

Yes, I agree, this is much more beautiful looking, and also is the format I noticed mysql workbench syntax's it too. I personally think that this should be the common practice syntax for this query. – DDeathlonger Aug 09 '20 at 08:02

score 15 · Answer 3 · answered Feb 15 '13 at 11:10

15

Try this:

UPDATE test
SET
   field = 1
WHERE id = 123 and condition

answered Feb 15 '13 at 11:10

Hamlet Hakobyan

32,965
6
52
68

5

I downvoted this again... as that is not even remotely a solution to the problem... it puzzles me how people seem to have upvoted this – Dominique Jan 29 '16 at 10:30
@Dominique, Maybe you have confused by error on word `condition`? – Hamlet Hakobyan Jan 29 '16 at 15:28
1

the problem with the WHERE condition is that it acts on the row level and not on the field level. If you have an update of few fields and you want a condition on one of them only then the WHERE will not work. It will either cause the row to be update or not, affecting the other fields as well. – Zvi Apr 06 '16 at 13:01
2

I don't have you confused with anything, I just know that your suggestions is completely wrong and not what the OP asked. You should read the question again – Dominique Sep 27 '16 at 07:20

score 4 · Answer 4 · edited Jun 28 '18 at 09:53

4

Another solution we can use MySQL IF() conditional function :

UPDATE test
SET  field  = IF(something == 1{CONDITION}, 1 {NEW VALUE}, field)
WHERE `id` = 5

edited Jun 28 '18 at 09:53

Manav

553
7
18

answered Jun 28 '18 at 09:14

krishna

98
5

`UPDATE servers SET port = IF(port != 21, 22, port) WHERE \`id\` = 123;` in my case i want to set ssh port to 22 if not only 21 telnet port else leave 22 – vimjet Oct 23 '18 at 23:42
3

Shouldn't it be single equals not double equals? – Rob Sedgwick Oct 29 '18 at 17:04

score 3 · Answer 5 · answered Apr 18 '17 at 20:42

3

Another variation:

UPDATE test
SET field = IF ( {condition}, {new value}, field )
WHERE id = 123

This will update the field with {new value} only if {condition} is met

answered Apr 18 '17 at 20:42

phoenix

1,629
20
11

This answer is also much better than the accepted one! – Heitor Jun 15 '20 at 05:09
okay i find this a great answer, though what would happen to performance, if this particular field is continuously updated – KaliBoy Mar 06 '21 at 16:24

score -2 · Answer 6 · edited Nov 21 '19 at 15:50

-2

found the solution with AND condition:

  $trainstrength = "UPDATE user_character SET strength_trains = strength_trains + 1, trained_strength = trained_strength +1, character_gold = character_gold - $gold_to_next_strength WHERE ID = $currentUser AND character_gold > $gold_to_next_strength";

edited Nov 21 '19 at 15:50

Willie Cheng

7,679
13
55
68

answered Aug 11 '19 at 08:36

Oldhuntersblood

37
8

MySQL: update a field only if condition is met

6 Answers6

Linked