CODEWITHSUNDEEP
oracle-databaseduplicates
Microsoft DN
Microsoft DN

Reputation: 10030

Removing duplicate data from a column

I have a table with following structure

    Id  Pro_id      name        price
    ----------------------------------------
    1   001          ABC        200
    1   002          XYZ        100
    1   003          XYZ        150
    2   004          PQR        100
    2   005          PQR        100
    2   006          LMN        200
    2   007          LMN        300
    2   008          DEF        150

As you can see there are some duplicate names in 'name' column.
I want to remove all the duplicate names(just need to keep first entered name and remove remaining)

So my table should look like- 

Id  Pro_id      name        price
----------------------------------------
1   001         ABC          200
1   002         XYZ          100
2   004         PQR          100
2   006         LMN          200
2   008         DEF          150

I tried following to get duplicate names-

SELECT ID, NAME, count(NAME) FROM TABLENAME
GROUP BY ID, NAME HAVING count(NAME)>1

But now I am unable to go further, stucked in how to delete the records.

any idea?

Upvotes: 0

Views: 93

Answers (4)

Sai
Sai

Reputation: 689

 DELETE FROM table_name
 WHERE rowid NOT IN
               ( 
                 SELECT MIN(rowid)
                 FROM table_name
                 GROUP BY column1, column2, column3...
               ) ;

Upvotes: 1

haki
haki

Reputation: 9779

You can try something like this 

delete from table1 
where rowid in 
( 
    select rid
    from  
        (
        select  rowid as rid, 
                row_number() over (partition by name order by pro_id) as rn
        from    table1
        )
    where rn > 1
)

Havent tested it

Upvotes: 0

kwelsan
kwelsan

Reputation: 1219

You may try below SQL (In MySQL it works) 

delete t1.* from tablename t1
    inner join
tablename t2 ON t1.name = t2.name
    AND t1.Pro_id > t2.Pro_id

Upvotes: 2

Oleg Sklyar
Oleg Sklyar

Reputation: 10092

There is no "first" in SQL as the order of select is generally undefined, so the following will keep entries with the minimum value of Pro_id for duplicated names, but you are free to define a different aggregator:

DELETE FROM tablename 
WHERE Pro_id NOT IN (SELECT MIN(Pro_id) FROM tablename GROUP BY name);

Upvotes: 1

Related Questions