MySQL nested CASE error I need help with?
- by AK
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
