MySQL IF statement in UPDATE query

Question

Why cannot i use IF like this

UPDATE my_users
    IF(position2 = 18, "SET position = 17", ''),
    IF(position2 = 17, "SET position = 16", ''),
    IF(position2 = 16, "SET position = 15", ''),
        WHERE user_id => 170 AND user_id <= 1000

Only thing that works is

UPDATE my_users
    SET position2 = IF(position2 = 18, 17, 
                      IF(position2 = 17, 16, 
                        IF(position2 = 16, 15, ''
                      )
                        )
                          )
        WHERE user_id => 170 AND user_id <= 1000

is there way to make same thng in any other way because i have more than 40 conditions for that field position2 and its easy to get lost in this way?

Edit:

If i run

UPDATE my_users
    SET position2 = CASE position2  WHEN 18 THEN 17
                    WHEN 17 THEN 16
                    WHEN 16 THEN 15
                    END
            WHERE user_id => 170 AND user_id <= 1000

when position2 is anything not in CASE statement it sets it to nothing, how can i just keep any value other than what in CASE statement the same

Edit 2: I guess solution is:

   UPDATE my_users
        SET position2 = CASE position2  WHEN 18 THEN 17
                        WHEN 17 THEN 16
                        WHEN 16 THEN 15
                            ELSE position2
                        END
                WHERE user_id => 170 AND user_id <= 1000

Edit 3: If my request was not one time thing the more faster request would be as suggested Vatev

   UPDATE my_users
        SET position2 = CASE position2  WHEN 18 THEN 17
                        WHEN 17 THEN 16
                        WHEN 16 THEN 15
                        END
                WHERE user_id => 170 AND user_id <= 1000
                AND position2 >= 16 and <= 18

Btw if you want to decrement the column you can just SET position2 = position2-1;. — Vatev
– Vatev, Commented Jul 28, 2012 at 0:01
just add ELSE clause, like ... ELSE position2 END. Or just update only those rows where position2 IN (16,17,18) with position2-1, it'd be better. — raina77ow
– raina77ow, Commented Jul 28, 2012 at 0:54
The proper way would be to add AND position2 IN (16,17,18) to your WHERE clause. You can also put a 'default' CASE like this CASE position2 WHEN .. THEN .. ELSE position2 END. — Vatev
– Vatev, Commented Jul 28, 2012 at 0:54

Maverick · Accepted Answer · 2014-02-03 17:59:21Z

4

You can also do something like this:

                UPDATE my_users
                     SET position2 = CASE WHEN position2 =18 THEN 17
                        WHEN position2 =17 THEN 16
                        WHEN position2 =16 THEN 15 ELSE position2 
                        END
                WHERE user_id between 170 AND  1000
                AND position2 between 16 and  18

answered Feb 3, 2014 at 17:59

Maverick

1,1851 gold badge8 silver badges16 bronze badges

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

Comments

chrislondon · Accepted Answer · 2013-04-10 19:41:19Z

2

This is how I would rewrite your query:

UPDATE
  my_users
SET 
  position2 = position2 - 1
WHERE
  position2 >= 16
  AND position2 <= 18
  AND user_id >= 170 
  AND user_id <= 1000

answered Apr 10, 2013 at 19:41

chrislondon

12.6k5 gold badges29 silver badges66 bronze badges

Collectives™ on Stack Overflow

MySQL IF statement in UPDATE query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related