converting multiple update queries into case statement
hi,
i am converting forums at the moment, and have decided to create some new membergroups in the new forum, based on dates (past, present, future). these dates are based on start and end dates of our volunteers, in custom fields added to the pre-built table.
i can write the update queries easily enough, and will set up a php file and cron job to automate it weekly, but i just can't get the syntax for a case statement to work properly - it needs to look at the users startdate and/or enddate, change the membergroup as appropriate, but leave any other user's membergroup alone.
i know i've left out the ELSE part below, but that's because i just don't know how to write it to leave the rest of the records alone.
UPDATE `smf_members`
SET id_group =
CASE
WHEN (CURDATE() < startdate)
THEN id_group = '24'
WHEN (CURDATE() BETWEEN startdate AND enddate)
THEN id_group = '23'
WHEN (CURDATE() > enddate)
THEN id_group = '20'
END
WHERE
(CURDATE() < startdate)
OR
(CURDATE() BETWEEN startdate AND enddate)
OR
(CURDATE() > enddate)
i'll also need to extend the query, to alter the 'additional_groups' field, but would like to prioritise this.
===reference====
future vols = 24
present vols = 23
past vols = 20
cheers :)