access update query sql multiple values

Question

I've searched for a solution, but have not yet found one that works...

I'm trying to update multiple values in a column based on distinct values in another column. For example:

If status = F05 then statusID = 987
If status = F12 then statusID = 12957

I've tried this with no success:

UPDATE myTable
SET statusID = CASE status
   WHEN 'F05' THEN 987
   WHEN 'F12' THEN 12957
END

There are thousands that need updating so, of course, I'd like to run this in a single update query.

What am I missing? What am I doing wrong?

Thanks!

Answer 1

In access you can use the SWITCH function. The CASE statement doesn't work.

UPDATE myTable
SET statusID = 
SWITCH 
   ( [status] = 'F05', 987,
     [status] = 'F12', 12957)  

However if you have too many items you might want to create a Mapping table who's data looks like

OldStatus | NewStatus
---------------------
F05       | 987
F12       | 12957

And then perform the following

UPDATE 
       myTable  
       INNER JOIN Mapping ON myTable.Status  = Mapping.OldStatus
 SET 
        myTable.Status = Mapping.NewStatus;