IIF update query

[H0MELY]

Greetings, thank you for looking at my question. Here is my situation.
Everyday a user receives a feed (table) of devices that have had a change in
the last day, such as address, city, state, servicer, etc...

The feed contains all changes, however only certain changes are actually
important. I created a query that grabs the pertenant fields and then also
creates a field to hold a flag for that field. It is simply a yes no field.

So structually there would be a device_id, address and address_flag. I
would like to run an update query that simply compares the address field of
the feed and the address field in the system and if there is a change updates
the address flag to "y", if there is no change it either updates the field to
"n" or makes no change to the field.

Any ideas would be greatly appreciated. If there are any logic ideas to
accomplish teh identification better that too would be appreciated.
Basically the feed usually sends between 10 and 50 devices a day, so there
isn't a great deal of processing that needs to take place. Thanks in advance
for any assistance you have to offer.

-John

 

[KenSheridan via AccessMonster.com]

Something like this might be suitable;

UPDATE Devices
SET address_flag = TRUE
WHERE EXISTS
(SELECT *
FROM Feed
WHERE Feed.device_id = Devices.device_id)
AND NOT EXISTS
(SELECT *
FROM Feed
WHERE Feed.device_id = Devices.device_id
AND Feed.address = Devices.address);

The first subquery establishes that a row exists in the Feed table with the
same device_id as the current row in the Devices table; the second query
establishes that the values in the address column in each differ for the
device. If this is the case then the address_flag column in the Devices
table is updated to TRUE. I've assumed the column is of Boolean (yes/no)
data type rather than of text data type with possible values of 'Y' or 'N'.
Your post is a little ambiguous in this respect.

Ken Sheridan
Stafford, England