CODEWITHSUNDEEP
mysql
Joe
Joe

Reputation: 7

mysql query help for delete entries from table

i have 2 msql tables (records and players) whitch contains the following fields:

records = Id  PlayerId  Score  Date  Checkpoints  

players = Id Login Game NickName Nation UpdatedAt Wins TimePlayed TeamName

i deleted few entrys from player db, and i will remove this value from records table.

the Id value is same in both db.

i used this command: 

DELETE FROM `players` WHERE `players`.`Id` = 27;

but after deleted the Id` = 27 still exist in records table.

So now dont have id 27 in players table but i will remove it from records. and i have 120 more entryes :(

Upvotes: 1

Views: 338

Answers (3)

jetblack
jetblack

Reputation: 590

DELETE FROM records WHERE NOT PlayerId IN (SELECT Id FROM players)

This would delete all records that doesn't belong to players.

Upvotes: 0

Adds
Adds

Reputation: 631

Try this with the Inner join

DELETE records, players  FROM records  INNER JOIN players  
WHERE records.Id= players.Id and records.Id = '27'

this should delete form both the tables

this if you the record doesnt exist in one table : 

 DELETE records, players  FROM records  INNER JOIN players  
 WHERE records.Id != players.Id

Upvotes: 0

Barmar
Barmar

Reputation: 780984

If you want related rows to be deleted automatically from the records table when you delete from players, you need to declare PlayerId as a foreign key with the ON DELETE CASCADE option.

ALTER TABLE records ADD CONSTRAINT fk_playerid FOREIGN KEY (PlayerId) REFERENCES players (id) ON DELETE CASCADE;

This will only work if you're using InnoDB; foreign keys are ignored in MyISAM.

If you can't use a foreign key, you can join the tables when deleting:

DELETE p, r FROM players AS p
LEFT JOIN records AS r ON p.id = r.PlayerId
WHERE p.id = 27;

This needs to use LEFT JOIN; if you use INNER JOIN, it won't delete players that have no related records.

Upvotes: 1

Related Questions