Delete Query Question

[Guest]

I have two tables whereby Table1's ID field (autonumber) is linked (1 to
many) on the Table2 fldID field.

It is possible for records from Table2 to be deleted without disturbing the
"parent" record in Table1. I would like to get rid of the parent record in
Table1 if there are no corresponding records in Table2. The problem is
slightly more complicated in that I cannot delete all records in Table1 with
no records in Table2. A couple of conditions for the Table1 records must be
set.

For example:
DELETE the record(s) from Table1 WHERE Table1.fld2 <>"Value" AND Table1.fld3
= "value" AND WHERE Table2.fldID is Null (those that do not have any
corresponding records in Table2).

An Unmatched Query correctly identifies the records to be deleted but I
can't get the delete query to work.

 

[MGFoster]

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

Perhaps this (untested):

DELETE t1.*
FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.fldid = t2.fldid
WHERE t1.fld2 <> "value"
AND t1.fld3 = "value"
AND t2.fldID IS NULL

or this:

DELETE *
FROM table1 as t1
WHERE t1.fld2 <> "value"
AND t1.fld3 = "value"
AND fldID NOT IN (SELECT fldID FROM table2)

or use this in the WHERE clause:

AND fldID NOT EXISTS (SELECT * FROM table2 WHERE fldID = t1.fldID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkCM7oechKqOuFEgEQLzhACfaSzIHPtjyT/4xBViqjg+LSaLuYYAoIpp
PJbi1Jq6IfxgbB61mq4J4DUc
=PUA8
-----END PGP SIGNATURE-----

 

[Guest]

Thanks MGFoster!

Your second suggestion worked perfectly. The first was similar to what I had
tried and couldn't get to work. But number two was magic!

Thanks again!