daZza daZza - 2 months ago 4
SQL Question

How to delete all records returned by a subquery?

I want to delete all records that are returned by a certain query, but I can't figure out a proper way to do this. I tried to

DELETE FROM mytable WHERE EXISTS (subquery)
, however, that deleted all records from the table and not just the ones selected by the subquery.

My subquery looks like this:

SELECT
MAX(columnA) as columnA,
-- 50 other columns
FROM myTable
GROUP BY
-- the 50 other columns above
having count(*) > 1;


This should be easy enough, but my mind is just stuck right now. I'm thankful for any suggestions.

Edit: columnA is not unique (also no other column in that table is globally unique)

Answer

Presumably, you want to use in:

DELETE FROM myTable
    WHERE columnA IN (SELECT MAX(columnA) as columnA
                      FROM myTable 
                      GROUP BY -- the 50 other columns above 
                      HAVING count(*) > 1
                     );

This assumes that columnA is globally unique in the table. Otherwise, you will have to work a bit harder.

DELETE FROM myTable t
    WHERE EXISTS (SELECT 1
                  FROM (SELECT MAX(columnA) as columnA,
                               col1, col2, . . .
                        FROM myTable 
                        GROUP BY -- the 50 other columns above 
                        HAVING count(*) > 1
                       ) t2
                  WHERE t.columnA = t2.columnA AND
                        t.col1 = t2.col1 AND
                        t.col2 = t2.col2 AND . . .
                 );

And, even this isn't guaranteed to work if any of the columns have NULL values (although the conditions can be easily modified to handle this).

Comments