records existing in Main table not found in Temp Table (1 Viewer)

[casey]

Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.

1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.

Need Help Here...
2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While
Temp table:
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a
1 - "STK" - a
2 - "CHK" - a

Main table
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a - LEAVE (EXISTS In Both Tables)
1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp)
1 - "STK" - a - LEAVE (EXISTS In Both Tables)
1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp)
3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)

Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.

I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.

Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this???
SELECT D.*
FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber)
WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;

THANKS.

 

[casey]

Sorry for the confusing question.

Can someone help me substitue QTS_TEMP For:
SELECT DISTINCT lngStoreNumber, strCadFile FROM Temp
in the following query?


SELECT D.lngStoreNumber, D.lngComponentSerial, D.strCADFile
FROM (QTS_TEMP AS T LEFT JOIN Main AS D ON (T.lngStoreNumber = D.lngStoreNumber) AND (T.strCadFile = D.strCADFile)) LEFT JOIN Temp AS S ON D.lngComponentSerial = S.lngComponentSerial
WHERE S.lngStoreNumber is null

I keep getting an error when I try it.

Thanks.

 

[casey]

Got It!!!

SELECT D.lngStoreNumber, D.lngComponentSerial, D.strCADFile
FROM ((SELECT DISTINCT lngStoreNumber, strCadFile
FROM Temp) AS T LEFT JOIN Main AS D ON (T.lngStoreNumber = D.lngStoreNumber) AND (T.strCadFile = D.strCADFile)) LEFT JOIN Temp AS S ON D.lngComponentSerial = S.lngComponentSerial
WHERE S.lngStoreNumber is null


Thanks to anyone who looked at this.