MySQL nested CASE error I need help with?
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 ju开发者_如何学Gost 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
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.
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 ...
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
精彩评论