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

Was more surprised because typically the L2 community keeps to its self. Was a bit odd though reading the question and thinking "that looks like... hmm... it is!" :) — Corbin, Dec 22 '11 at 02:52
@Corbin I totally see what you mean. Interesting enough, I'm getting help on a L2 question to a work project. — Marco Aurélio Deleu, Mar 30 '16 at 13:50

ThinkingStiff · Accepted Answer · 2011-12-22T19:05:20.683

255

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

edited Dec 22 '11 at 19:05

answered Dec 22 '11 at 02:34

ThinkingStiff

64,767
30
146
239

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 -------------------------------------------------- – JoinOG Dec 22 '11 at 02:50
In your error it looks like your are using two different server names for `spawnlist`. I see `l2revo.root.spawnlist` and `db.root.spawnlist`. – ThinkingStiff Dec 22 '11 at 02:56
i just make a mistake pasting it here, but the user name and db name are same , at my error. – JoinOG Dec 22 '11 at 02:57
Try adding `AS` for your aliases. – ThinkingStiff Dec 22 '11 at 03:01
I just updated the code. Also, try removing the parens on the `WHERE` clause. – ThinkingStiff Dec 22 '11 at 03:05
[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 – JoinOG Dec 22 '11 at 03:07
What version of MySQL are you running? – ThinkingStiff Dec 22 '11 at 03:12
Try adding `.*` at the end of `spawnlist` on the first line. I updated the code above. – ThinkingStiff Dec 22 '11 at 03:14
[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 Dec 22 '11 at 03:17
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. – ThinkingStiff Dec 22 '11 at 03:23
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 ? – JoinOG Dec 22 '11 at 03:30
It could just be taking a long time to delete. Maybe there's a lot of data. – ThinkingStiff Dec 22 '11 at 03:33
@DreamFactory It will. It won't work without the .*, which is what he had at first. – ThinkingStiff Jun 20 '12 at 18:59
4

@GauravRamanan the s.* tells mysql what to DELETE, you don't want to delete rows from the JOINED table – Julz Mar 29 '17 at 10:45

Dan · Answer 2 · 2011-12-22T03:10:59.223

14

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

edited Dec 22 '11 at 03:10

answered Dec 22 '11 at 02:28

Dan

11,914
14
49
112

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 Dec 22 '11 at 02:40
Changed, might be more successful now? – Dan Dec 22 '11 at 02:51
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 -------------------------------------------------- – JoinOG Dec 22 '11 at 02:54
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"); – Corbin Dec 22 '11 at 02:57
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. – Dan Dec 22 '11 at 03:03
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 Dec 22 '11 at 03:06
oh , :( its so hard to delete data from InnoDB tables :( – JoinOG Dec 22 '11 at 03:12
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? – Dan Dec 22 '11 at 03:14
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? – JoinOG Dec 22 '11 at 03:19
You could create a view of a combination of both tables then delete from the view where `type = "monster";` – Dan Dec 22 '11 at 03:21
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) – Xenos May 16 '19 at 20:27

Aylian Craspa · Answer 3 · 2013-02-18T07:23:28.170

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.

`type` is actually in the other table, not in the `spawnlist` table, so join is necessary — vitro, Apr 02 '20 at 20:43

SQL DELETE with INNER JOIN

3 Answers3

Linked