Deleting records where two fields match...

  • Thread starter Thread starter Trevor
  • Start date Start date
T

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
 
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
 
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
 
Back
Top