Update Multiple fields with multiple criteria

[rockell333]

I have 3 different fields I am trying to update.
The first two are below - type and description.

based on the deliverability code I need the description and address to be different things. I havent listed them all here. What am I doing wrong?




UPDATE [Copy Of NIXIES]


SET
OLD_Related_Address_Type = "Old Address", OLD_Related_Address_Description = "Moved No Forwarding Address"
WHERE
DELIVERABILITY_CODE = 'K'
OLD_Related_Address_Type = "Bad Address", OLD_Related_Address_Description = "Attempted, Not Known"
WHERE
DELIVERABILITY_CODE = 'A';

[JoeM]

You cannot do it like that. First, you cannot have multiple WHERE clauses like that. The word "WHERE" should only show up once in a query, unless you have a subquery.

If instead of making three different simple UPDATE queries (one for each set of criteria),you want to try to do it in a single query, take a look at this thread here:
http://www.pcreview.co.uk/threads/ca...query.1174562/

If you have issues getting it to work, post back.

[rockell333]

ok.. i get that.. Now can i set two fields based on the same criteria?

[rockell333]

Not Working ???

UPDATE Copy_Of_NIXIES
SET [OLD_Related_Address_Type] =
SWITCH
(DELIVERABILITY_CODE LIKE 'K',"Old Address",
DELIVERABILITY_CODE LIKE 'A',"Bad Address",
DELIVERABILITY_CODE LIKE 'Q',"Bad Address",
DELIVERABILITY_CODE LIKE 'I',"Bad Address",
DELIVERABILITY_CODE LIKE 'V'),"Bad Address","Nothing"
)

[JoeM]

ok.. i get that.. Now can i set two fields based on the same criteria?

Yes, you can update multiple fields, just separate them with a comma.

So your structure would look like:

UPDATE [TableName]
SET [Field1]=..., [Field2]=...
WHERE [Field3]=...

If you had multiple fields in your Criteria, you would just use AND or OR.

[rockell333]

the "field 1" and "field 2" are different based on criteria... meaning

if the del code is k field 1 is old and field 2 vacant
if del code is Q field one is bad and field to is refused
if del code is S field one is bad and field to is bad number

[JoeM]

OK, here is a better way to go about it.

Make a lookup table, with two fields: DELIVERABILITY_CODE and OLD_Related_Address_Type.
Then populate this table with each possible DELIVERABILITY_CODE and the corresponding OLD_Related_Address_Type you want to return.
Then, link this Lookup table your main table, and use it in the Update Query, i.e.

UPDATE Copy_of_NIXIES 
INNER JOIN Lookup_Table 
ON Copy_of_NIXIES.DELIVERABILITY_CODE = Lookup_Table.DELIVERABILITY_CODE 
SET Copy_of_NIXIES.OLD_Related_Address_Type = [Lookup_Table]![OLD_Related_Address_Type];

[rockell333]

ahhh.. that makes sense. I will try that now

[rockell333]

PERFECT... thank you...