EXISTS with UPDATE

Copper Contributor

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

 

 

 

0 Replies