Delete duplicate rows from SELECT query?

Question

I'm working with SQLite and currently attempting to delete certain duplicate rows from a certain user (with ID 12345). I've managed to identify all of the rows I wish to delete, but I'm now unsure how to go about deleting these rows. This is my SELECT query:

SELECT t.*
from (select t.*, count(*) over (partition by code, version) 
as cnt from t) t
where cnt >= 2 and userID = "12345";

How would I go about deleting the rows matching this result? Can I use the query above in some way to identify which rows I want to delete? Thanks in advance!

forpas · Accepted Answer · 2020-11-06 21:02:03Z

1

You can get all the duplicate code, version combinations for userid = '12345' with this query:

select code, version
from tablename
where userid = '12345'
group by code, version
having count(*) > 1

and you can use it in the DELETE statement:

delete from tablename 
where userid = '12345'
and (code, version) in (
  select code, version
  from tablename
  where userid = tablename.userid
  group by code, version
  having count(*) > 1
)

If you want to keep 1 of the duplicate rows, use MIN() window function to get the row with the min rowid for each combination of code, version and exclude it from being deleted:

delete from tablename 
where userid = '12345'
and rowid not in (
  select distinct min(rowid) over (partition by code, version)
  from tablename
  where userid = tablename.userid
)

edited Nov 6, 2020 at 21:02

answered Nov 6, 2020 at 20:49

forpas

165k10 gold badges51 silver badges85 bronze badges

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

4 Comments

Californium Over a year ago

I see. Would it be possible to only delete one of the duplicate rows if the user 12345 has 2 rows with the same value?

Californium Over a year ago

Can this work if one of the duplicate rows is "owned" by another user than 12345? Or must both rows be "owned" by 12345?

Californium Over a year ago

I understand. What would I need to modify in order to have it working with rows "owned" by both 12345 and another user? Only deleting the row owned by 12345.

forpas Over a year ago

This is a different requirement for which you should post a new question where you must explain what you want.

Gordon Linoff · Accepted Answer · 2020-11-06 20:37:41Z

0

Hmmm . . . you don't have to use window functions:

delete from t
    where exists (select 1
                  from t t2
                  where t2.code = t.code and t2.version = t.version
                  group by code, version
                  having count(*) >= 2
                 );

answered Nov 6, 2020 at 20:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Californium Over a year ago

What is t2 in this case?

Gordon Linoff Over a year ago

@Californium . . . It is a table alias for the reference to your (unnamed) table in the subquery. I note that this answer was 12 minutes earlier than the other answer.

Collectives™ on Stack Overflow

Delete duplicate rows from SELECT query?

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related