Page 1 of 1
[MySQL] Delete duplicate entries with less score *SOLVED*
Posted: Wed Mar 14, 2007 12:06 pm
by benny
Hi.
I have a MySQL question. Maybe someone has can help me here.
Let's say we have the following table
Code: Select all
[b]highscore[/b]
+--------+-------+
| name | score |
+--------+-------+
| bob | 10 |
| bob | 20 |
| anne | 13 |
| anne | 15 |
+--------+-------+
Is there a single SQL-Statement that deletes all duplicate entry concerning
the
name where the score is less the maximum score.
So, that the remaining table would look like this :
Code: Select all
[b]highscore[/b]
+--------+-------+
| name | score |
+--------+-------+
| bob | 20 |
| anne | 15 |
+--------+-------+
I would think about using PHP with getting all the different names using
the
DISTINCT command and then gettint the maximum score
of each username and then delete all entries of the user with less score.
Nevertheless, I asked myself if this could be handled by just one clever
SQL-Statement
Any help appreciated.
Re: [MySQL] Delete duplicate entries with less score
Posted: Wed Mar 14, 2007 12:28 pm
by traumatic
Hi benny,
hmm... like this?
Code: Select all
DELETE highscore FROM highscore t1, highscore t2
WHERE t1.score < t2.score AND t1.name = t2.name
However you shouldn't run into this problem at all.
Firstly I'd check if the new highscore is higher than the one
already present. Then, depending on the result, I'd decide
whether to make an UPDATE or INSERT.
That would make things much less complicated IMHO.
Posted: Wed Mar 14, 2007 12:37 pm
by benny
@traumatic:
Thanks. I'll try this.
And you are of course right. Problem is that I have to fix an online game
with a giving database. So, all there are already so many entries in that
table that I have to "clean" it one time without deleting the highest scores.
Then, of course, I will check it each time before writing new highscores...
Posted: Wed Mar 14, 2007 12:44 pm
by traumatic
Ok, got it!
Be sure to let me know if the query worked for you.
Posted: Wed Mar 14, 2007 12:50 pm
by benny
I am trying your statement with
SELECT instead of
DELETE to
be on the safe side before I delete something I dont want to delete.
The following didnt work :
Code: Select all
SELECT playerscore FROM tx_r21memgame_highscore t1, tx_r21memgame_highscore t2
WHERE t1.playerscore < t2.playerscore AND t1.playername = t2.playername AND
t1.playername = '83423'
But if I change "playerscore" to "*" I got all entries with less score. So it seems
to work. Thanks a lot !!!
Posted: Wed Mar 14, 2007 12:52 pm
by traumatic
Always do a backup beforehand!
Glad it seems to work. You're welcome, Benny.
Posted: Wed Mar 14, 2007 12:54 pm
by benny
Yup. I dumped the table before. Nevertheless. I am not very safe when it
comes to databases and sql to be honest. Thats why I am double careful
right now.
Thanks a lot again. Traumatic. And of course greetings, mate!
Posted: Wed Mar 14, 2007 6:18 pm
by benny
Ok. Just to finish this thread.
I successfully deleted all the entries as I intended to do ... What traumatic
posted in his first thread did work for 100%. Didn't has to change any line
So thanks again mate.
Problem solved. /me happy
Posted: Wed Mar 14, 2007 8:56 pm
by traumatic
Cool!
/me too