sql server mseq: Hello,

I'm trying to compare data between tables for the purpose
of performing a large update. The first table has 210
fields (consumer) of which I need to compare 3 of them
(last, address, zip) to a second table (do_not_mail) that
only has those 3 fields (last, address, zip).

The purpose for doing this is, if any record in my large
table matches a record in the small table, I need to
update a field in the large table.

This is the query I'm using but it feels very inefficient
and I'd like some feedback or suggestions:

UPDATE consumer SET mail_pander_flag='Y'
WHERE
last+address+zip IN
(SELECT last+address+zip FROM do_not_mail);


Thank you in advance.

hi chris,

[quoted text, click to view]
(SELECT last+address+zip FROM do_not_mail);<<

do not use above clause, it will not be efficient because it will not use
any indexes on the columns last/address/zip. Try either of following
queries, should be efficient than what you are doing. Also make sure you
have indexes on these 3 columns in both the tables.

update consumer set mail_pander_flag='Y'
where exists
(select * from do_not_mail a
where a.last = consumer.last
and a.address = consumer.address
and a.zip = consumer.zip)

-- or --

update a set mail_pander_flag='Y'
from consumer a join do_not_mail b
on a.last = b.last
and a.address = b.address
and a.zip = b.zip

--
Vishal Parkar
vgparkar@yahoo.co.in