Re: Comparing data in tables

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 03/13/04 

Date: Sun, 14 Mar 2004 00:27:46 +0530

hi chris,

>>last+address+zip IN
(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

Relevant Pages

  • Re: JOIN on multiple conditions
    ... You must use the OUTER JOIN syntax, of course, ... >but if you learn only the INNER JOIN syntax does it change the way you ... More complicated queries should not be written by ... condition is placed in the ON or in the WHERE clause. ...
    (microsoft.public.sqlserver.programming)
  • Re: error -2147217904 - how to fix :o( ?
    ... If the saved queries don't have WHERE clauses, yes, the saved ... When I say on-the-fly SQL, ... use the base saved query and add a WHERE clause. ...
    (comp.databases.ms-access)
  • Re: how to use partitions once created?
    ... Your example queries worked perfectly. ... is a Where clause and a slice the same thing - semantically ... > Please do not send email directly to this alias. ...
    (microsoft.public.sqlserver.olap)
  • Re: OT: where to get mySQL questions answered?
    ... >queries for a search engine, I'm experiencing more crippling problems as ... >soon as I add an OR to my WHERE x LIKE '%blah%' type queries. ... >If more than one word is typed in, it adds to the WHERE clause. ...
    (comp.lang.php)
  • Re: ADO.NET not retrieving results correctly
    ... > (Appointments INNER JOIN Buildings ON ... however the data adapter in my code returns 0 results. ... Is there something buggy with using the LIKE clause to ... > send queries through ADO? ...
    (microsoft.public.dotnet.framework.adonet)