CODEWITHSUNDEEP
sqloracle-databasecommand
user3352858
user3352858

Reputation: 1

How does this oracle SQL command works?

I know that, it removes all the duplicated rows, but I can't check it. How is the following piece of code working?

DELETE grades 
 WHERE rowid NOT IN (SELECT MAX(rowid) 
                       FROM grades 
                      GROUP BY grades);

Upvotes: 0

Views: 47

Answers (3)

Elfentech
Elfentech

Reputation: 747

DELETE grades WHERE rowid NOT IN (SELECT MAX(rowid) FROM grades GROUP BY grades);

The ROWID column is a hash value representing the hash of where the data is being stored. Oracle is used to incremend that value everytime a value is inserted into a table. Therefore, the last one is the maximum.

SELECT MAX(rowid) FROM grades GROUP BY grades

This query is therefore selecting the last value inserted in the table, and the group by allow you to indentify a single value among all same value of grades.

DELETE grades WHERE ...

Finally, you delete all the values that were not selected, that means all the values that are not the last inserted, all duplicates that were formelly inserted.

Upvotes: 1

Cedric Simon
Cedric Simon

Reputation: 4659

In Oracle, rowid are never duplicated. The rowid is the unique (internal) row identifier for Oracle.

Since I imagine the grade tables is not a huge table, and is not involved in foreign key relationship, you could simply do this:

create table grades_temp as select distinct * from grades;
delete from grades;
insert into grades select * from grades_temp;
drop table grades_temp;

You should also declare a primary key and a unique index on the grades table to avoid future insert of duplicated data.

Upvotes: 0

Mark J. Bobak
Mark J. Bobak

Reputation: 14385

Well, apparently, you have a table GRADES, with a column GRADES. The SELECT will identify the max rowid per grades (GROUP BY GRADES). This gives you one single row per unique value for GRADES. Then, the DELETE will delete any row that's not in the list of rowids you generated from the SELECT.

The end result is, you end up with only one row per unique GRADES value. This means all duplicates will be removed.

Hope that's clear.

Upvotes: 1

Related Questions