Problems with Delete Query

M

MarkS

I am confused with the following Query. I would like to delete all records
where the ITNO, PO, Line, and Vendor fields are the same in two tables.

DELETE FROM tblReleased WHERE
(
ITNO IN (SELECT ITNO FROM tblEmailedLines)
AND PO IN (SELECT PO FROM tblEmailedLines)
AND LINE IN (SELECT LineNumber FROM tblEmailedLines)
AND VCODE IN (SELECT VendorCode FROM tblEmailedLines)
)

However the code seems to be deleting virtually all records and I cannot
figure out the logic. Can anybody shed some light on this or a better way of
constructing the Query.

Thanks in advance
 
G

giorgio rancati

Hi MarkS,

----
DELETE DISTINCTROW tblReleased.*
FROM tblReleased
INNER JOIN tblEmailedLines
ON (tblReleased.ITNO = tblEmailedLines.ITNO) AND
(tblReleased.PO = tblEmailedLines.PO) AND
(tblReleased.VCODE = tblEmailedLines.VCODE) AND
(tblReleased.LINE = tblEmailedLines.LINE);
----

Bye
Giorgio

"MarkS" ha scritto:
 
G

giorgio rancati

Ops ,
I confused two names of fields :)

---
DELETE DISTINCTROW tblReleased.*
FROM tblReleased
INNER JOIN tblEmailedLines
ON (tblReleased.ITNO = tblEmailedLines.ITNO) AND
(tblReleased.PO = tblEmailedLines.PO) AND
(tblReleased.VCODE = tblEmailedLines.VendorCode) AND
(tblReleased.LINE = tblEmailedLines.LineNumber);
----

bye
Giorgio


"giorgio rancati" ha scritto:
 
M

MarkS

Thank you Giorgio for your help with this.

Mark

giorgio rancati said:
Ops ,
I confused two names of fields :)

---
DELETE DISTINCTROW tblReleased.*
FROM tblReleased
INNER JOIN tblEmailedLines
ON (tblReleased.ITNO = tblEmailedLines.ITNO) AND
(tblReleased.PO = tblEmailedLines.PO) AND
(tblReleased.VCODE = tblEmailedLines.VendorCode) AND
(tblReleased.LINE = tblEmailedLines.LineNumber);
----

bye
Giorgio


"giorgio rancati" ha scritto:
Click to expand...
 
P

peregenem

MarkS said:
I would like to delete all records
where the ITNO, PO, Line, and Vendor fields are the same in two tables.

DELETE FROM tblReleased WHERE
(
ITNO IN (SELECT ITNO FROM tblEmailedLines)
AND PO IN (SELECT PO FROM tblEmailedLines)
AND LINE IN (SELECT LineNumber FROM tblEmailedLines)
AND VCODE IN (SELECT VendorCode FROM tblEmailedLines)
)

However the code seems to be deleting virtually all records and I cannot
figure out the logic.
Click to expand...

DELETE FROM tblReleased WHERE
EXISTS (
SELECT *
FROM tblEmailedLines
WHERE tblEmailedLines.ITNO = tblReleased.ITNO
AND tblEmailedLines.PO = tblReleased.PO
AND tblEmailedLines.LineNumber = tblReleased.LINE
AND tblEmailedLines.VendorCode = tblReleased.VCODE
);
 
Top