Need query to select a recordset and delete it

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with three fields, num, inb, and del. When records are
created with my application, num is never null, and either inb or del, but
not both, can be null, or they both can contain a value. If a record
contains a value x for num, and both inb and del are not null, then there
should be no other records where num = x, inb is not null and del is null.
These illegal records need to be deleted. So, the query should filter for
records that have del = null and num matches num of records where both del
and inb are not null, and then deletes those records.
 
Try:

Try the following SQL String:

DELETE Main.*
FROM [YourTable] AS Main
WHERE
( Main.num IN
(
SELECT Sub.num
FROM [YourTable] AS Sub
WHERE (Sub.inb Is Not Null) AND (Sub.del Is Not Null)
)
)
AND (Main.inb Is Not Null)
AND (Main.del Is Null)
 
Back
Top