CODEWITHSUNDEEP
sqloracle-database
c12
c12

Reputation: 9827

Remove Duplicate Row Data

I need to remove duplicates from my table (user_info). I always want to remove the row with the id column that is lower of the two rows being returned from my select/having query below. Any ideas on how to write the delete statement to remove the duplicates (lower user_info.id column) from the results of my select/having query below? I'm using Oracle 11g.

user_info table structure:

id (unique primary key number 10 generated by sequence)
user_id (number 10)
first_name (varchar2)
last_name (varchar2)

data example:

id      user_id
______  ___________
37265   1455
265798  1455

sql to show duplicates:

select user_id, count(*)
  from user_info
 group by user_id
HAVING count(*) > 1

Upvotes: 0

Views: 148

Answers (3)

redsoxlost
redsoxlost

Reputation: 1235

drop table test;
/

create table test
(
ids number,
user_id number
);
/


insert into test
values(37265,1455);
/

insert into test
values(265798,1455);
/


select * from test;

delete from test t
where t.ids  < (select max(ids) from test t1 where T1.USER_ID= T.USER_ID)

This query employs the sub query to do the same !

Upvotes: 0

Raj More
Raj More

Reputation: 48048

Start with this to show you only the duplicates

Select user_id, count(*) NumRows, Min(Id) SmallestId, Max(Id) LargestId
From user_info
Group by user_id
HAVING count(*) > 1

This will show you the min and max for each user_id (with the same value for SmallestId and LargestId if there are no duplicates.

Select user_id, count(*) NumRows, Min(Id) SmallestId, Max(Id) LargestId
From user_info
Group by user_id

For a User, you want to keep the MaxId and Delete everything else. So you can write a DELETE statement to be

DELETE From user_info
Where Id Not IN
(
    Select Max(Id)
    From user_info
    Group by user_id
)

This will get the

Upvotes: 1

Joseph B
Joseph B

Reputation: 5679

You can use the following query:

DELETE 
FROM user_info
WHERE id NOT IN
    (SELECT MAX(id)
    FROM user_info
    GROUP BY user_id);

This query will delete all the duplicate rows except the user_id row with maximum id.

Here's a SQL Fiddle which demonstrates the delete.

Upvotes: 3

Related Questions