Forum Discussion
EXISTS with UPDATE
I have two tables. Each one has a field named Bad_Address. I have a stack of letters that have come back undeliverable. The addresses for the letters are in either Table1 or Table2. For each letter I have to mark the Bad_Address field for that address record as 1 meaning it's a bad address. I wont know which table the address record is in when I'm doing this. So I start with a simple union query to find the table and bring back a unique identifier. Now I know the unique identifier and the table the record is in. So far so good.
BUT I don't want to have two update queries, one for each table. I want a union query that will use the unique identifier to set the Bad_Address field for that record and WHERE EXISTS to know what table it's in based on the unique identifier.
Something like (if I knew what I was doing):
UPDATE the table
Set Bad_Address = 1
WHERE EXISTS Unique_ID = 'ABC123'
or
UPDATE WHERE EXIST Unique_ID = 'ABC123'
Set Bad_Address = 1
The unique id will never be in both tables.
I know I'm close. Can somebody finish this one for me. Also, there's a reason why I'm doing the search first and then the Update.
Thanks,
tod