Question 20798 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Case of IF? – Mysql

   SELECT f.user_id, f.item_id, f.status, f.shipped,
     CASE
      WHEN f.status = 0 THEN UPDATE f SET f.status = 1 WHERE f.user_id = 1 AND f.item_id = 1
      WHEN f.status = 1 THEN UPDATE f SET f.status = 0 WHERE f.user_id = 1 AND f.item_id = 1
      ELSE 'ERROR'
    END CASE
   FROM business AS f;

Hi, I want to update the status of an item based on its value ( 0 or 1). I still got syntax error. Am I using the right conditional statements or should I use IF()? If so, how can I put another query inside the IF()?

Thank you!

Tags:

2

Answers


  1. 0

    This is very simple SQL query:

    UPDATE business b
SET b.status = CASE WHEN b.status = 1 THEN 0 ELSE 1 END 
WHERE b.user_id = 1 AND b.item_id = 1;

SELECT * FROM  business;
    Login or Signup to reply.
  2. 0

    If you want to update the actual data in the table, then use an UPDATE statement, not a SELECT statement:

    UPDATE business
SET status = 
        CASE WHEN status = 1 THEN 0 
             WHEN status = 0 THEN 1
             ELSE -1 --Likely this has to be numeric since you are setting a numeric column, right? 
             END
WHERE user_id = 1 AND item_id = 1;

    Running this will update your business table for the record(s) where user_id = 1 and item_id=1 switching the values of status unless the current status value isn’t 1 or 0, in which case it will set status to -1. Note that the string 'error' can’t be used a value unless this column is a varchar or similar string-type column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search