Simple compare query

[nickdel]

I say simple and it probably is but I'm just not seeing it!

I have 2 tables which have a unique identifier (DebtID). I know that in Table 1 there are DebtID's that do NOT appear in Table 2. I need to be able to identify these and add a "flag".

I'm using this but it takes an age probably because of the NOT operator:

UPDATE CleanExtract
SET Renewal = true
WHERE NOT DebtID in (SELECT DebtID FROM DI_Clean);

 

[PHV]

Perhaps this ?
UPDATE CleanExtract
SET Renewal = TRUE
WHERE DebtID IN (SELECT C.DebtID FROM CleanExtract C LEFT JOIN DI_Clean D
ON C.DebtID = D.DebtID WHERE D.DebtID IS NULL)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nickdel]

Absolute star, thank you. This has went from 10 minutes to a 10 second run time!