SQL DELETE with INNER JOIN

Question

There are 2 tables  spawnlist and npc  and I need to delete data from spawnlsit  npc templateid   n idTemplate is the only thing that  connect  the tables  I have tried this script but it doesn t work   I have tried this   DELETE s FROM spawnlist s INNER JOIN npc n ON s npc templateid   n idTemplate WHERE  n type    monster

User · Answer

If the database is InnoDB then it might be a better idea to use foreign keys and cascade on delete, this would do what you want and also result in no redundant data being stored.

For this example however I don't think you need the first s:

DELETE s 
FROM spawnlist AS s 
INNER JOIN npc AS n ON s.npc_templateid = n.idTemplate 
WHERE n.type = "monster";

It might be a better idea to select the rows before deleting so you are sure your deleting what you wish to:

SELECT * FROM spawnlist
INNER JOIN npc ON spawnlist.npc_templateid = npc.idTemplate
WHERE npc.type = "monster";

You can also check the MySQL delete syntax here: http://dev.mysql.com/doc/refman/5.0/en/delete.html

User · Answer

if the database is InnoDB you dont need to do joins in deletion  only   DELETE FROM spawnlist WHERE spawnlist type    monster     can be used to delete the all the records that linked with foreign keys in other tables  to do that you have to first linked your tables in design time   CREATE TABLE IF NOT EXIST spawnlist     npc templateid VARCHAR 20  NOT NULL PRIMARY KEY   ENGINE InnoDB   CREATE TABLE IF NOT EXIST npc     idTemplate VARCHAR 20  NOT NULL     FOREIGN KEY  idTemplate  REFERENCES spawnlist npc templateid  ON DELETE CASCADE   ENGINE InnoDB    if you uses MyISAM you can delete records joining like this   DELETE a b FROM  spawnlist  a JOIN  npc  b ON a  npc templateid    b  idTemplate  WHERE a  type     monster     in first line i have initialized the two temp tables for delet the record  in second line i have assigned the existance table to both a and b but here i have linked both tables together with join keyword  and i have matched the primary and foreign key for both tables that make link  in last line i have filtered the record by field to delete

User · Answer

Add    to s in your first line    Try     DELETE s   FROM spawnlist s INNER JOIN npc n ON s npc templateid   n idTemplate WHERE  n type    monster

[mysql] SQL DELETE with INNER JOIN

Examples related to mysql

Examples related to sql

Examples related to sql-server

Examples related to inner-join

Examples related to sql-delete