By JoinOG


2011-12-22 02:25:23 8 Comments

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

3 comments

@Aylian Craspa 2013-02-18 07:00:05

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.

@ThinkingStiff 2011-12-22 02:34:07

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

@JoinOG 2011-12-22 02:50:57

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 2011-12-22 02:56:12

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 2011-12-22 02:57:37

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

@ThinkingStiff 2011-12-22 03:01:11

Try adding AS for your aliases.

@JoinOG 2011-12-22 03:03:05

Database is InnoDB

@ThinkingStiff 2011-12-22 03:05:36

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

@JoinOG 2011-12-22 03:07:38

[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 2011-12-22 03:12:06

What version of MySQL are you running?

@ThinkingStiff 2011-12-22 03:14:57

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

@JoinOG 2011-12-22 03:17:45

[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

@ThinkingStiff 2011-12-22 03:21:34

That's all I've got. I looks right. Sorry.

@ThinkingStiff 2011-12-22 03:23:38

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 2011-12-22 03:30:02

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 2011-12-22 03:33:34

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

@JoinOG 2011-12-22 03:39:45

yes it worked ty.

@Gaurav Ramanan 2012-06-20 17:51:56

Its working.... But why can't it work without the s Alias?

@ThinkingStiff 2012-06-20 18:59:03

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

@Julz 2017-03-29 10:45:28

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

@Dan 2011-12-22 02:28:58

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

@JoinOG 2011-12-22 02:40:12

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 2011-12-22 02:51:28

Changed, might be more successful now?

@JoinOG 2011-12-22 02:54:16

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

@Dan 2011-12-22 02:56:21

Third time lucky ..

@Corbin 2011-12-22 02:57:25

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 2011-12-22 02:59:54

Sorry but i got same error like before.

@Dan 2011-12-22 03:03:22

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.

@JoinOG 2011-12-22 03:06:07

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

@Dan 2011-12-22 03:11:19

This the last version I can think of ..

@JoinOG 2011-12-22 03:12:09

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

@Dan 2011-12-22 03:14:06

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?

@JoinOG 2011-12-22 03:19:57

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 2011-12-22 03:21:00

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

@Xenos 2019-05-16 20:27:38

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)

Related Questions

Sponsored Content

12 Answered Questions

[SOLVED] When should I use cross apply over inner join?

37 Answered Questions

3 Answered Questions

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

Sponsored Content