How do I Make this a delete Query ??

[cvegas]

I have found the records I need in an umateched records query and now want to
delete them from the table. I reviewed subqueries but don't understand what
the syntax should be to get these deleted.

Any help would be appreciated. Thanks in advance for any help you can
provide.

SELECT [tblOrderExtraCharges].[OrderID]
FROM tblOrderExtraCharges LEFT JOIN Orders ON
[tblOrderExtraCharges].[OrderID]=[Orders].[OrderID]
WHERE ((([Orders].[OrderID]) Is Null));

 

[Ken Sheridan]

Try this:

DELETE *
FROM tblOrderExtraCharges
WHERE NOT EXISTS
(SELECT *
FROM Orders
WHERE Orders.OrderID = tblOrderExtraCharges.OrderID);

Be sure you have the table backed up first.

Ken Sheridan
Stafford, England

 

[MGFoster]

I have found the records I need in an umateched records query and now want to
delete them from the table. I reviewed subqueries but don't understand what
the syntax should be to get these deleted.

Any help would be appreciated. Thanks in advance for any help you can
provide.

SELECT [tblOrderExtraCharges].[OrderID]
FROM tblOrderExtraCharges LEFT JOIN Orders ON
[tblOrderExtraCharges].[OrderID]=[Orders].[OrderID]
WHERE ((([Orders].[OrderID]) Is Null));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

DELETE tblOrderExtraCharges.*
FROM tblOrderExtraCharges LEFT JOIN Orders ON
[tblOrderExtraCharges].[OrderID]=[Orders].[OrderID]
WHERE [Orders].[OrderID] Is Null

Or, a slower query:

DELETE *
FROM tblOrderExtraCharges
WHERE OrderID NOT IN (SELECT OrderID FROM Orders)

The best way to avoid this scenario (orphaned records) is to set up a
Foreign Key between Orders.OrderID and tblOrderExtraCharges.OrderID with
ON DELETE CASCADE option. Then no records can be put into
tblOrderExtraCharges w/o there also being the same OrderID in Orders.
And everytime an OrderID is deleted in Orders it will also be deleted in
tblOrderExtraCharges. To do this use the Relationships window (on the
menu bar: Tools > Relationships. Drag column names between tables.).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDf80IechKqOuFEgEQLp3wCgvxOFHZQe3uWJpDkGdikiNGriaF4AoIMd
YReYC6Ol1hle7DEcpdnwdRZB
=ZHhM
-----END PGP SIGNATURE-----