hardus.lombaard (8/29/2012)
update aset a.Flag = 0
from table1 a
WHERE
a.OLD_NUMBER in (select number from table2) and
a.NEW_NUMBER not in (select number from table2)
The above query sets the flag column to 0 if OLD_NUMBER is found in table2 and NEW_NUMBER is not found in table2. The problem with this query is table2 has about 3,2 million numbers. The sub query "select number from table2" takes about 15-20 seconds to execute. This query therefore takes way to long (about 50 min if table1 has 6000 records). Is there a more efficient, faster way to accomplish the same?
50 minutes suggests you don't have a usable index on column [number] of table2. With only 6000 rows in table1, indexing will matter far less.
Here's the simple join equivalent of your query;
UPDATE a
SET a.Flag = 0
FROM table1 a
INNER JOIN table2 t2a ON t2a.number = a.OLD_NUMBER
LEFT JOIN table2 t2b ON t2b.number = a.NEW_NUMBER
WHERE t2b.number IS NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden