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
– Corbin, Commented Dec 22, 2011 at 2: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
– Marco Aurélio Deleu, Commented Mar 30, 2016 at 13:50

ThinkingStiff · Accepted Answer · 2011-12-22 19:05:20Z

265

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, 2011 at 19:05

answered Dec 22, 2011 at 2:34

ThinkingStiff

65.4k31 gold badges148 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

JoinOG Over a year ago

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 --------------------------------------------------

ThinkingStiff Over a year ago

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

JoinOG Over a year ago

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

ThinkingStiff Over a year ago

Try adding AS for your aliases.

Julz Over a year ago

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

|

Dan · Accepted Answer · 2011-12-22 03:10:59Z

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, 2011 at 3:10

answered Dec 22, 2011 at 2:28

Dan

12.2k14 gold badges55 silver badges118 bronze badges

11 Comments

JoinOG Over a year ago

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 --------------------------------------------------

Dan Over a year ago

Changed, might be more successful now?

JoinOG Over a year ago

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 --------------------------------------------------

Corbin Over a year ago

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

Dan Over a year ago

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.

|

2 revs · Accepted Answer · 2013-02-18 07:23:28Z

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

Collectives™ on Stack Overflow

SQL DELETE with INNER JOIN

3 Answers 3

14 Comments

11 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

11 Comments

1 Comment

Linked

Related