MySQL nested CASE error I need help with?

Question

What I am trying to do here is: IF the records in table todo as identified in $done have a value in the column recurinterval then THEN reset date_scheduled column ELSE just set status_id column to 6 for those records.

This is the error I get from mysql_error() ...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE recurinterval != 0 AND recurinterval IS NOT NULL THEN SET date_sche' at line 2

How can I make this statement work?

UPDATE todo 
CASE recurinterval != 0 AND recurinterval IS NOT NULL THEN
SET date_scheduled = CASE recurunit
WHEN 'DAY' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval DAY)
WHEN 'WEEK' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval WEEK)
WHEN 'MONTH' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval MONTH)
WHEN 'YEAR' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval YEAR)
END
WHERE todo_id IN ($done) 
ELSE 
SET status_id = 6 WHERE todo_id IN ($done)
END

The following mySQL statement worked just fine before I revised like above.

UPDATE todo 
SET date_scheduled = CASE recurunit
WHEN 'DAY' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval DAY)
WHEN 'WEEK' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval WEEK)
WHEN 'MONTH' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval MONTH)
WHEN 'YEAR' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval YEAR)
END
WHERE todo_id IN ($done) 
AND recurinterval != 0 
AND recurinterval IS NOT NULL

Answer 1

I believe you need to add 'WHEN' after the first CASE keyword. So the beginning of the first line reads:

UPDATE todo CASE WHEN recurinterval !=0 AND ...

Answer 2

The thing is that you're trying to do something that afaik is impossible to do using just one query. You want to update 1 of 2 columns based on the value of your recurinterval field. So basically you should split it up into 2 queries, the first one will be the one you had ie.

UPDATE todo 
SET date_scheduled = CASE recurunit
WHEN 'DAY' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval DAY)
WHEN 'WEEK' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval WEEK)
WHEN 'MONTH' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval MONTH)
WHEN 'YEAR' THEN DATE_ADD(date_scheduled, INTERVAL recurinterval YEAR)
END
WHERE todo_id IN ($done) 
AND recurinterval != 0 
AND recurinterval IS NOT NULL

And the second one will be for the other rows :

UPDATE todo 
SET status_id = 6
WHERE todo_id IN ($done) 
AND (recurinterval = 0 
OR recurinterval IS NULL)

If you run these queries (most likely you'll want to run them inside a transaction so you can rollback the changes if an error occurs during one of the queries) you should get the result you want.

Answer 3

Seems to me that your first case in you modified statement CASE recurinterval != 0 AND recurinterval IS NOT NULL THEN should be actually an IF