CODEWITHSUNDEEP
sqloracle-databasesql-delete
daZza
daZza

Reputation: 1679

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)

Upvotes: 3

Views: 7367

Answers (3)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

Another solution if the uniqueness is only guaranteed by a set of columns: 

delete table1 where (col1, col2, ...) in (
    select min(col1), col2, ...
    from table1 
    where...
    group by col2, ...
)

Null values will be ignored and not deleted.

To achieve this, try something like 

with data (id, val1, val2) as 
(
select 1, '10', 10 from dual union all
select 2, '20', 21 from dual union all
select 2, null, 21 from dual union all
select 2, '20', null from dual 
)
-- map null values in column to a nonexistent value in this column
select * from data d where (d.id, nvl(d.val1, '#<null>')) in 
(select dd.id, nvl(dd.val1, '#<null>') from data dd)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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).

Upvotes: 5

Aleksej
Aleksej

Reputation: 22949

If you need to delete all the rows of a table such that the value of a given field is in the result of a query, you can use something like

delete table
my column in ( select column from ...)

Upvotes: -1

Related Questions