Query Deleting Records in One Table by using "NOT IN " clause to check with multiple table. Can this query be more simplified?

Question

Right now i am using the below query

 string myQry = "DELETE FROM myMainTbl AS a WHERE
    a.id NOT IN(SELECT b.mainid FROM mySubTbl_1 AS b WHERE b.mainid = a.id GROUP BY b.mainid HAVING MAX(b.sttime) > @mydate)
    AND
    a.id NOT IN(SELECT c.mainid FROM mySubTbl_2 AS c WHERE c.mainid = a.id GROUP BY c.mainid HAVING MAX(c.sttime) > @mydate)
    AND
    a.id NOT IN(SELECT d.mainid FROM mySubTbl_3 AS d WHERE d.mainid = a.id GROUP BY d.mainid HAVING MAX(d.sttime) > @mydate)
   AND
   a.id NOT IN(SELECT e.mainid FROM mySubTbl_4 AS e WHERE e.mainid = a.id GROUP BY e.mainid HAVING MAX(e.sttime) > @mydate)
   AND
   a.id NOT IN(SELECT f.mainid FROM mySubTbl_5 AS f WHERE f.mainid = a.id GROUP BY f.mainid HAVING MAX(f.sttime) > @mydate)";

Can it be more simplified?

Short-answer: you can't. MS Access does not fully implement SQL-92 (even Access 2019!) and it does not support using a `JOIN` in a `DELETE`, see here: https://stackoverflow.com/questions/5585732/how-to-delete-in-ms-access-when-using-joins — Dai, Dec 23 '20 at 11:05
As far as I know, MS Access does not support T-SQL style named paremeters (e.g. `@mydate`) so your query won't work anyway. **Please confirm that you are using JET Red or ACE in MS Access** or if you're using Access as a frontend for SQL Server or another ODBC/OLE-DB database. — Dai, Dec 23 '20 at 11:06
It looks as though you're wanting to prevent unintentional deletes of principal entities in foreign-key relationships. The way to do that is by having enforced FK constraints - that way you don't need to filter your `DELETE` statement: instead the DBMS will simply throw an error if it will violate a constraint. — Dai, Dec 23 '20 at 11:07

score 1 · Answer 1 · answered Dec 23 '20 at 12:40

I recommend NOT EXISTS over NOT IN. This looks like:

NOT EXISTS (SELECT 1 FROM mySubTbl_1 AS b WHERE b.mainid = a.id AND b.sttime > @mydate)

If no matching row exists after the date, then the maximum doesn't exist either.

Then, you can add indexes to optimize this query: mySubTbl_1(mainid, sttime).

In terms of your query, create the indexes and use:

DELETE FROM myMainTbl AS a
    WHERE NOT EXISTS (SELECT b.mainid FROM mySubTbl_1 AS b WHERE b.mainid = a.id AND b.sttime > @mydate) AND
          NOT EXISTS (SELECT c.mainid FROM mySubTbl_2 AS c WHERE c.mainid = a.id AND c.sttime > @mydate) AND
          NOT EXISTS (SELECT d.mainid FROM mySubTbl_3 AS d WHERE d.mainid = a.id AND d.sttime > @mydate) AND
          NOT EXISTS (SELECT e.mainid FROM mySubTbl_4 AS e WHERE e.mainid = a.id AND e.sttime > @mydate) AND
          NOT EXISTS (SELECT f.mainid FROM mySubTbl_5 AS f WHERE f.mainid = a.id AND f.sttime > @mydate)";

score 1 · Accepted Answer · answered Dec 23 '20 at 12:42

You can rewrite this to an NOT EXISTS, which would avoid having to worry about groups and aggregating, and union the data to simplify the query:

DELETE 
FROM myMainTbl AS a 
WHERE
    NOT EXISTS(
        SELECT 1 FROM (
            SELECT mainid, sttime
            FROM mySubTbl_1 
            UNION ALL
            SELECT mainid, sttime
            FROM mySubTbl_2 
            UNION ALL
            SELECT mainid, sttime
            FROM mySubTbl_3
            UNION ALL
            SELECT mainid, sttime
            FROM mySubTbl_4 
            UNION ALL
            SELECT mainid, sttime
            FROM mySubTbl_5
        ) b
        WHERE b.mainid = a.id AND sttime > @mydate
    )

This is also semantically much more clear to me, you want to delete all rows where no related row exists in one of the subtables with a time greater than your parameter.

Thanks a lot, its working nicely and simple and quicker by Union. — Murulimadhav, Dec 23 '20 at 14:23

Query Deleting Records in One Table by using "NOT IN " clause to check with multiple table. Can this query be more simplified?

2 Answers2