can you delete duplicate rows of a select query?

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
)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top