2

I have two tables:

tbl_players
tbl_highscores

I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by highscore. I can gather the information with JOIN like this:

SELECT scorePoints, playerAlias, playerEmail, playerCountry
    FROM tbl_highscores JOIN tbl_players ON tbl_highscores.playerId = tbl_players.playerId 
    WHERE playerCountry = 'lt' 

But how do I delete all highscores that are saved by a user from a certain language?

  • I'm not sure I understand your question, if you already know which player's data to delete, why does it matter the country? Or you meant to say: "how do I delete all highscores that are saved by ALL USERS from a certain language"? – Marian Apr 2 '12 at 13:48
4
DELETE B.* FROM
(
    SELECT playerId FROM tbl_players 
    WHERE playerCountry = 'lt'
) A INNER JOIN tbl_highscores B
USING (playerId);

Make sure you have this index

ALTER TABLE tbl_players ADD INDEX CountryIDIndex (playerCountry,playerId);
1

To delete the records in tbl_highscores for all players with a specific playerCountry:

DELETE FROM tbl_highscores
WHERE playerId IN (
    SELECT playerId 
    FROM tbl_players
    WHERE playerCountry = 'lt')
  • 2
    That will be very slow in MySQL. IN (SELECT) is evaluated outside->in, so for every row in tbl_highscores, the subselect will be re-executed. – Aaron Brown Apr 2 '12 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.