Deleting Records in Master Table joined to 2nd table

[LearningSql]

Hi all -- I'm still learning, fun but frustrating -- thanks in advance.

I have a Master table with a Primary key consisting of 3 fields. I have incoming data on a daily basis that will replace duplicate records. I would like to follow the process below -- I'm still in development so this can change.

- Import new data to tblNewImported
- Remove any records from tblMaster that have a match between tblNewImported and tblMaster . (I did a join on the 3 fields that comprise the Primary Key for each record in one instance -- I used a Where in another instance)
- Insert the new record from tblNewImported into Table Master

I am trying to figure out how to delete the records from tblMaster however I am receiving various errors depending on which code I have. Any help or suggestions is appreciated.

Here's the 2 pieces of code I ended up with after about 3 hours of frustration:

DELETE FROM tblMaster INNER JOIN
tblNewImported ON tblMaster.NODE_ID = tblNewImported.NODE_ID AND
tblMaster.ORDER_NUMBER = tblNewImported.ORDER_NUMBER AND
tblMaster .RECEIVED_DATE = tblNewImported.RECEIVED_DATE

I've also tried:
DELETE FROM tblMaster WHERE (tblMaster.NODE_ID = tblNewImported.NODE_ID) AND (tblMaster.ORDER_NUMBER = tblNewImported.ORDER_NUMBER) AND (tblMaster.RECEIVED_DATE = tblNewImported.RECEIVED_DATE)

Thanks in advance.

 

[tlbroadbent]

Try one of these.

DELETE tblMaster
WHERE EXISTS
(SELECT * FROM tblNewImported
WHERE NODE_ID = tblMaster.NODE_ID
AND ORDER_NUMBER = tblMaster.ORDER_NUMBER
AND RECEIVED_DATE = tblMaster.RECEIVED_DATE)

DELETE tblMaster
FROM tblMaster
INNER JOIN tblNewImported
ON tblMaster.NODE_ID = tblNewImported.NODE_ID
AND tblMaster.ORDER_NUMBER = tblNewImported.ORDER_NUMBER
AND tblMaster.RECEIVED_DATE = tblNewImported.RECEIVED_DATE Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[LearningSql]

Thanks Terry. I tried the 2nd one first and it worked.
I appreciate the assistance.