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!

Your UPDATE query looks fine. Describe "I've tried this with no success". What happened? — Adriano Carneiro
– Adriano Carneiro, Commented Nov 29, 2011 at 16:51

Conrad Frix · Accepted Answer · 2011-11-29 19:45:46Z

7

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;

edited Nov 29, 2011 at 19:45

answered Nov 29, 2011 at 17:22

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kern mann Over a year ago

This is working very well. I've exported the table, used Excel to concatenate the fields in the format of the code, and brought it back in as my update query. What an enormous time saver! Thank you for this gift! ~kern

kern mann Over a year ago

Oh no... :) SWITCH worked great for my first set, however it gives an "expression too complex" error if I use more than 15 items... How can I perform this en masse?

Conrad Frix Over a year ago

@kern mann Yuck well you can try a mapping table approach. See updated answer

Collectives™ on Stack Overflow

access update query sql multiple values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related