CODEWITHSUNDEEP
mysqlmariadb
Hultin
Hultin

Reputation: 168

Delete duplicate rows (searched and tried what is on here already)

I've for a day or two tried to remove a couple of duplicate database rows that appeared in my system after a minor fail on my end, I've tried most (if not all) methods I've been able to find on Stackoverflow so far. I've had several of those methods work flawlessly previously on the same mariadb and mysql as I've tried to delete now.

This is my table structure.

ID | date | experience | characterid

ID is the unique identifier (aka Auto incremeting), each character ID should have only ONE entry per date, and that's what's been duplicated. Some characterids have 5-7 entries per date, identical dates (I've checked).

I've tried the following queries to remove them which have worked in the past for similar issues I've had.

DELETE n1 FROM experiencehistory n1, experiencehistory n2 WHERE n1.id > n2.id AND n1.date = n2.date AND n1.characterid = n2.characterid

aswell as 

DELETE experiencehistory FROM experiencehistory

LEFT OUTER JOIN ( SELECT MIN(id) as RowId, characterid, date FROM experiencehistory GROUP BY characterid, date ) as KeepRows ON experiencehistory.id = KeepRows.RowId WHERE KeepRows.RowId IS NULL

Both produce no results in console and through PHPMyadmin I get this error;

Static analysis:

1 errors were found during analysis.

Unexpected token. (near "experiencehistory" at position 7)

Any suggestions on what I could do to resolve this or if I did something wrong with these methods I've tried primarily?

Serverversion: 10.1.13-MariaDB - mariadb.org binary distribution

Thankful for any insight!

Upvotes: 0

Views: 85

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You can try this way (delete all id != for min(id) group by characterid, date

DELETE FROM experiencehistory
where (id, characterid, date) not in 
      ( select x_id, characterid , x_date from  ( select  min(x.id) as x_id
                  , x.characterid as x_characterid 
                 , x.date  as x_date
          from  experiencehistory  as x group by x.characterid, x.date ) t );

Upvotes: 1

Related Questions