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 "links" the tables. I tried this script, but it does not work.

I tried this:

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

Answer 1

Add .* 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"); 

Answer 2

If the database is InnoDB, then it would be better to use foreign keys and cascade when deleting, this would do what you want and also should not contain redundant data.

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 better to select the lines before deleting to make sure that you delete what you want:

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

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

Answer 3

if the database is InnoDB, you do not need to do joins when deleting. only

 DELETE FROM spawnlist WHERE spawnlist.type = "monster"; 

can be used to delete all records associated with foreign keys in other tables; to do this, you must first link your tables at 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 use MyISAM you can delete entries like this

 DELETE a,b FROM `spawnlist` a JOIN `npc` b ON a.`npc_templateid` = b.`idTemplate` WHERE a.`type` = 'monster'; 

in the first row, I initialized two temporary tables to delete the record, in the second row I assigned an existence table for a and b, but here I linked both tables together with the join keyword, and I matched the primary and foreign key for both tables that make the link , in the last line, I filtered the record by field for deletion.