Update if else basic

Question

I am trying to get a MySQL update done, which should be very easy, when the query runs if the value is 0 then it will be set to 1, if it is 1 then it will be set to 0.

update agents set manual_import = 0 if manual_import =1,set manual_import = 1 if manual_import = 1

There are so many examples on there but which one is the easiest?

possible duplicate of [MySQL - Using If Then Else in MySQL UPDATE or SELECT Queries](http://stackoverflow.com/questions/2177543/mysql-using-if-then-else-in-mysql-update-or-select-queries) — , Mar 24 '15 at 10:40

score 2 · Answer 1 · answered Mar 24 '15 at 10:29

2

A bit of math can help:

UPDATE agents
SET manual_import = 1 - manual_import       # turn 1 to 0 and 0 to 1
WHERE manual_import IN (0, 1)               # but only when the value is 0 or 1

answered Mar 24 '15 at 10:29

axiac

68,258
9
99
134

score 1 · Answer 2 · answered Mar 24 '15 at 10:31

1

Ok, i have figured out this would be suitable for my own question and any other simple queries similar.

UPDATE agents SET manual_import = CASE
        WHEN manual_import = 1 THEN 0
        WHEN manual_import = 0 THEN 1    
        END
    WHERE a_id = $a_id

answered Mar 24 '15 at 10:31

Display name

420
5
20

1

If `manual_import` takes more than just the values 0 and 1, this command would set any other value to `NULL`. – eggyal Mar 24 '15 at 10:33

score 0 · Answer 3 · answered Mar 24 '15 at 10:34

You can try using case when then

update agents set
manual_import = case when manual_import = 1 then 0 else 1
end

I assumed that manual_import has only two values 0 and 1 and if it has more than just 1 and 0 then try this:

    UPDATE agents SET manual_import = case
    when manual_import = 1 then 0
    when manual_import = 0 then 1  else manual_import   
    END

score -1 · Answer 4 · edited May 23 '17 at 11:43

-1

In oracle database the esasiest way is the 'decode' function. As answered in this question you can use the 'if' as following:

update agents set manual_import = if(manual_import=1,0,1)

edited May 23 '17 at 11:43

Community

1
1

answered Mar 24 '15 at 10:31

vathek

531
2
9

If `manual_import` takes more than just the values 0 and 1, all values that aren't 1 (and not just 0) will become 1. – eggyal Mar 24 '15 at 10:32

Update if else basic

4 Answers4