Deleting records where two fields match...

[Trevor]

Hello All,

I have two tables. "New" will have many of the same
records as "OLD" plus some new ones. I want to delete all
those records from "New" that are already in "OLD",
thereby leaving me only those that are new. The problem
is that I need to base it on two fields being the same.
For instance, there are many records that have customer
number 123 or invoice number 567. However, I only want to
delete those records that have customer 123 AND invoice
number 567. In other words, if customer 123 occurs
in "OLD", I don't want to simply delete all occurrences of
customer 123 from "New". Only those that also have
matching invoice number. If anyone could help that would
be much appreciated.

Thanks,

Trevor

 

[Gerald Stanley]

DELETE N.*
FROM New N
WHERE N.invoiceNo IN (SELECT invoiceNo FROM Old O WHERE
O.customer = N.customer)

Hope This Helps
Gerald Stanley MCSD

 

[Gerald Stanley]

Apologies - missed a line. Try

DELETE N.*
FROM New N
WHERE N.invoiceNo IN (SELECT invoiceNo FROM Old O WHERE
O.customer = N.customer)
AND N.customer IN (SELECT customer FROM Old O WHERE
O.invoiceNo = N.invoiceNo)

Gerald Stanley MCSD