Remove rows with multiple duplicate columns from database table

Question

I want to remove the last row because userid and friendwith column is duplicated.

friendshipid    userid  friendwith  friendshipstatus
183             24      102          4
151             24      52           2
155             24      66           2
179             24      66           2

thanks.

why don't you have unique key constraints, instead of trying to delete the duplicates — Dhiraj
– Dhiraj, Commented Apr 15, 2012 at 9:06

Sandeep Rajoria · Accepted Answer · 2012-04-15 09:11:30Z

2

if you want to keep the latest friendship id then do something like this

CREATE TABLE temp_table AS (SELECT * FROM table);
DELETE FROM table WHERE friendshipid NOT IN (SELECT friendshipid FROM (SELECT * FROM temp_table ORDER BY friendshipid DESC) as temp_table GROUP BY userid, friendwith);
DROP TABLE temp_table ;

Or if you want to keep the oldest friendship id then do something like this

CREATE TABLE temp_table AS (SELECT * FROM table);
DELETE FROM table WHERE friendshipid NOT IN (SELECT friendshipid FROM (SELECT * FROM temp_table ORDER BY friendshipid ASC) as temp_table GROUP BY userid, friendwith);
DROP TABLE temp_table ;

answered Apr 15, 2012 at 9:11

Sandeep Rajoria

1,2498 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andomar · Accepted Answer · 2012-04-15 13:00:24Z

1

You could delete all rows for which another row exists with the same userid and friendswith, but a lower friendshipid. For example:

delete  dup
from    YourTable as dup
join    YourTable orig
on      orig.userid = dup.userid
        and orig.friendwith = dup.friendwith
        and orig.friendshipid < dup.friendshipid

Example at SQL Fiddle.

edited Apr 15, 2012 at 13:00

answered Apr 15, 2012 at 9:10

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Nesim Razon · Accepted Answer · 2012-04-15 09:05:48Z

0

select friendshipid  ,  userid  , friendwith  , friendshipstatus from table 
group by userid , friendwith

answered Apr 15, 2012 at 9:05

Nesim Razon

9,8043 gold badges38 silver badges48 bronze badges

Collectives™ on Stack Overflow

Remove rows with multiple duplicate columns from database table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related