Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

2 Answers 2

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);
share|improve this answer

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')
share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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