Problems with Delete Query

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

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:

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

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:

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:

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

Thank you Giorgio for your help with this.

Mark

"giorgio rancati" wrote:

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:

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:

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

MarkS wrote:
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.

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
);