duplicate database entries-CodePudding

I've accidentally managed to add duplicate entries into my database. The database contains a list of telephone numbers and they are routed via the information contained in the value field. The id field is unique per entry, and the UUID and username fields should be identical but shouldn't exist in the table more than once.

Data has been blanked in the screenshot for data protection.

The following command allowed me to identify I had duplicate entries which can be seen in the screenshot above.

select * uuid, count(*) from usr_preferences group by uuid having count(*) > 1;

I'm after some help on how I could delete entries where the UUID count is more than one but one entry must remain. deleting the duplicate UUID with the highest id number would be preferred.

Is there a way to display the results before deleting them?

MySQL version - mysql Ver 14.14 Distrib 5.7.38-41, for Linux (x86_64) using 6.2

Thanks

CodePudding user response：

Could you give the following bit of code a go? Please make sure you have the database backed up before running this.

DELETE b FROM `test` a, `test` b where b.uuid = a.uuid and b.id > a.id;

I've expanded on your text data to make sure it will remove both duplicates and triplicates leaving the lowest ID. You can find my testing at this DB Fiddle.

https://www.db-fiddle.com/f/sUr6V6UP9tZ1Ya8eESid33/0

Hope this sorts you issue.

CodePudding user response：

Try the following for MySQL v5.7:

set @rn=0;
set @uuid=null;
delete from usr_preferences where id in 
(
  select D.id
    from
     (
        select id, uuid,
        case 
          when @uuid <> uuid then
          @rn:=1
          else
          @rn:=@rn 1
        end as rn,
        @uuid:=uuid
        from usr_preferences order by id,uuid
      ) D
  where D.rn>1
);

Select * From usr_preferences;

See a demo from db-fiddle.

Important Note: Test the query before using it on your table, and take a backup of your table before running this query on it.

For MySQL v8.0 and above you may try the following:

with cte as 
(
   select id, row_number() over (partition by uuid order by id) as rn
   from usr_preferences
)
delete U From
usr_preferences U join cte C
On U.id = C.id 
where C.rn > 1;