Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL – trying to find duplicated data

I have a database with a fair amount of duplicated data, i’ve tried a few ways if found on here but struggling with this.

As you can see there are multiple in the ‘owner’ but diferent in ‘type’ and there are some duplicates of both.

End result, i want to delete the duplicated data as an example, id 11,245

enter image description here

SELECT owner, COUNT(*) type FROM user_licenses GROUP by owner HAVING type > 1

SELECT owner, type FROM table GROUP BY type HAVING COUNT(*) > 1;

Tags:

2

Answers


  1. 0 
    delete from your_table
where id not in
(
  select * from
  (
      select max(id)
      from your_table
      group by owner, type
      having count(*) > 1
  ) tmp
)
    Login or Signup to reply.
  2. 0

    I would recommend a self-join:

    delete l
from user_licenses l
inner join (
    select owner, type, max(id) id
    from user_licenses l1
    group by owner, type
    having count(*) > 1
) l1 on l1.owner = l.owner
    and l1.type  = l.type 
    and l1.id    > l.id

    The subquery identifies the duplicates and their max id, then the outer query deletes rows with the same owner/type and a smaller id.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search