Combine more than 1 UPDATE's

[jongag1]

Hello,

I have serveral queries in Access:
UPDATE nawt SET field1 = '4' WHERE CODE='4';
UPDATE nawt SET field1 = '8' WHERE CODE='8';
UPDATE nawt SET field1 = '1' WHERE CODE='1';
UPDATE nawt SET field1 = '10' WHERE CODE='10';
UPDATE nawt SET field1 = '3' WHERE CODE='3';
UPDATE nawt SET field1 = '9' WHERE CODE='9';
UPDATE nawt SET field1 = '5' WHERE CODE='5';
UPDATE nawt SET field1 = '7' WHERE CODE='7';
UPDATE nawt SET field1 = '6' WHERE CODE='6';
UPDATE nawt SET field1 = '2' WHERE CODE='2';

I would like to combine the into one query in MS Access.
Is there an easy way to do that?

Kind regards,
Age de Jong

 

[DaOtH]

Yep:

UPDATE nawt SET field1 = CODE

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[jongag1]

Sorry, I changed some values in the example.

They are for CODE=4 field1 must be something like 0,1234, for CODE=8 field1 must be 0,4354 and the same for the other codes.

Regards,
Age

 

[DaOtH]

Create a new table with something like the following :

tblCodes
CODE   Value
4      0,1234
8      0,4354
10     0,8123
12     0,7831
...

And the statement will something like

UPDATE A
SET A.field1 = B.Value
FROM nawt A
INNER JOIN
tblCodes B
ON A.Code = B.Code

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963