update query with multiple values

Question

I want to write an update query in MS Access 2003. I have a field called product_code.

If product code is (between 110 and 752) OR (between 910 and 1124), I want to update product code=15.
If product code is (between 1210 and 1213) OR (1310 and 1423) I want to assign product code=16.
If product code is some other value I will assign 18, and so on

I don't think I can use CASE statement for this since I have many values to be updated. I tried to use multiple UPDATE/SET statements but it didn't work.

just to be clear, you want to consolidate product code 110-752 and 910-1124 into the product code 15? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Jan 2, 2011 at 14:44

Tom B · Accepted Answer · 2011-01-02 14:50:15Z

3

You could create a temporary table to hold your ranges and values. Call it TableB with 3 fields LowId, HighId, NewId

UPDATE TableA SET TableA.ProductID=TableB.NewId
WHERE TableA.ProductID>=TableB.LowID AND TableA.ProductID<=TableB.HighId

answered Jan 2, 2011 at 14:50

Tom B

2,1902 gold badges13 silver badges15 bronze badges

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

1 Comment

Fionnuala Over a year ago

This is the answer I would choose, unless I only had one or two different numbers to change.

LittleBobbyTables - Au Revoir · Accepted Answer · 2011-01-02 15:01:30Z

2

You can use nested IIF statements to achieve this:

UPDATE  Products
SET Product_Code = IIF(
    (Product_Code BETWEEN 110 and 752) or 
        (Product_Code BETWEEN 910 AND 1124), 
    15,  
    IIF(
        (Product_Code BETWEEN 1210 and 1213) or 
        (Product_Code BETWEEN 1310 AND 1423), 
        16, 18
       )
    )

What this does is if your Product Code is between 110 and 752 or 910 and 1124, it will update the Product_Code to 15. Otherwise, it checks to see if your code is between 1210 and 1213 or 1310 and 1423, in which case it uses 16. Otherwise, it uses 18.

NOTE: If you are updating an ID like this, make sure you have a backup of the database, or at least the table, before running this, as the previous values will be overwritten.

edited Jan 2, 2011 at 15:01

answered Jan 2, 2011 at 14:53

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

2 Comments

ozlem Over a year ago

Thanks for the quick reply. I tried your codes and it asks for the parameter value. I am just a beginner, so I don't know what this is!?

LittleBobbyTables - Au Revoir Over a year ago

@Ozlem -- yes, my table name was just an example since you didn't specify a table name or show any code. You'll have to modify the query to use your table name.

Collectives™ on Stack Overflow

update query with multiple values

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related