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");

Solution 1

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");

Solution 2

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

Solution 3

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.

Comments

[JoinOG]

This is the error i get : [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's INNER JOIN npc n ON s.npc_templateid = n.idTemplate WHERE n.type = "monste' at line 1 [Err] DELETE FROM spawnlist s INNER JOIN npc n ON s.npc_templateid = n.idTemplate WHERE n.type = "monster"; [Msg] Finished - Unsuccessfully --------------------------------------------------

[JoinOG]

Here is the error i got : [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spawnlist FROM db.root.spawnlist s INNER JOIN db.root.npc n ON s.npc_t' at line 1 [Err] DELETE l2revo.root.spawnlist FROM db.root.spawnlist s INNER JOIN db.root.npc n ON s.npc_templateid = n.idTemplate WHERE (n.type = "monster"); [Msg] Finished - Unsuccessfully --------------------------------------------------

[Dan]

Changed, might be more successful now?

[JoinOG]

Error: [Err] 1066 - Not unique table/alias: 'npc' [Err] DELETE spawnlist FROM spawnlist, npc INNER JOIN npc WHERE spawnlist.npc_templateid = npc.idTemplate AND npc.type = "monster"; [Msg] Finished - Unsuccessfully --------------------------------------------------

[ThinkingStiff]

In your error it looks like your are using two different server names for spawnlist. I see l2revo.root.spawnlist and db.root.spawnlist.

[Corbin]

If you're just going to run it once, you could run the horribly inefficient: DELETE FROM spawnlist WHERE npc_templateid IN (SELECT idTemplate from npc WHERE type = "monster");

[JoinOG]

i just make a mistake pasting it here, but the user name and db name are same , at my error.

[ThinkingStiff]

Try adding AS for your aliases.

[Dan]

That's my last attempt, if your deleting from just one table on a join then I can't see why that won't work.

[ThinkingStiff]

I just updated the code. Also, try removing the parens on the WHERE clause.

[JoinOG]

Idk, tested again and the error is the same: [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN npc ON spawnlist.npc_templateid = npc.idTemplate WHERE npc.type = '' at line 2

[JoinOG]

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spawnlist FROM InnoDB.root.spawnlist AS s INNER JOIN InnoDB.root.npc AS n ON' at line 1

[ThinkingStiff]

What version of MySQL are you running?

[JoinOG]

oh , :( its so hard to delete data from InnoDB tables :(

[Dan]

Still no luck? Why don't you just set up a foreign key and when you delete from one table it removes from the other using cascade?

[ThinkingStiff]

Try adding .* at the end of spawnlist on the first line. I updated the code above.

[JoinOG]

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spawnlist.* FROM InnoDB.root.spawnlist AS s INNER JOIN InnoDB.root.npc AS n ' at line 1

[JoinOG]

Hmm, is possible to make a code, that will delete the data from both tables ? cus i can restore 1 of them later. can you help pls?

[Dan]

You could create a view of a combination of both tables then delete from the view where type = "monster";

[ThinkingStiff]

Actually. One more thing. Go back to your original query in your question and try adding .* to the s so: DELETE s.* and leave everything else the same.

[JoinOG]

i have changed what you said, there is no error,but the query dont want to finish, it is still in process right now, whats wrong ?

[ThinkingStiff]

It could just be taking a long time to delete. Maybe there's a lot of data.

[ThinkingStiff]

@DreamFactory It will. It won't work without the .*, which is what he had at first.

[Julz]

@GauravRamanan the s.* tells mysql what to DELETE, you don't want to delete rows from the JOINED table

[Xenos]

You shouldn't split an atomic query into two potentially-non-atomic queries. Here, you would need a transaction + you would need to SELECT ... FOR UPDATE, unless, you won't write-lock the selected rows and another query from another session might DELETE some of them (not to mention that maybe 90% of devs will send the ids list to their client code, say Java or PHP, and resend them to the SQL with a utterly slow and ugly WHERE id IN (...) statement)

[vitro]

type is actually in the other table, not in the spawnlist table, so join is necessary