CODEWITHSUNDEEP
oracle-databaseoracle11g
Akash
Akash

Reputation: 5012

Deleting duplicate rows in oracle

Shouldn't the following query work fine for deleting duplicate rows in oracle

SQL> delete from sessions o 
      where 1<=(select count(*) 
                  from sessions i 
                 where i.id!=o.id 
                   and o.data=i.data);

It seems to delete all the duplication rows!! (I wish to keep 1 tough)

Upvotes: 0

Views: 548

Answers (1)

Tebbe
Tebbe

Reputation: 1372

Your statement doesn't work because your table has at least one row where two different ID's share the same values for DATA.

Although your intent may be to look for differing values of DATA ID by ID, what your SQL is saying is in fact set-based: "Look at my table as a whole. If there are any rows in the table such that the DATA is the same but the ID's are different (i.e., that inner COUNT(*) is anything greater than 0), then DELETE every row in the table."

You may be attempting specific, row-based logic, but your statement is big-picture (set-based). There's nothing in it to single out duplicate rows, as there is in the solution Ollie has linked to, for example.

Upvotes: 2

Related Questions