CODEWITHSUNDEEP
sqlmysqlif-statementsql-update
Caballero
Caballero

Reputation: 12101

MySQL: update field only if condition is met

Is it possible to do UPDATE query on 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, "field" would be only updated if condition is met, otherwise nothing is done.

UPDATE: my example query didn't exactly specify my problem in full, because there might be more fields to be updated without conditions, 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

Sorry for the lack of info.

Upvotes: 95

Views: 213565

Answers (6)

Oldhuntersblood
Oldhuntersblood

Reputation: 57

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";

Upvotes: -2

Dominique
Dominique

Reputation: 1120

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.

Upvotes: 54

krishna
krishna

Reputation: 98

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

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

Upvotes: 4

fedorqui
fedorqui

Reputation: 289505

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.

Upvotes: 170

kirilclicks
kirilclicks

Reputation: 1677

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

Upvotes: 3

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

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

Upvotes: 12

Related Questions