By alex


2010-07-26 03:05:00 8 Comments

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this

DELETE FROM `pets` p,
            `pets_activities` pa
      WHERE p.`order` > :order
        AND p.`pet_id` = :pet_id
        AND pa.`id` = p.`pet_id`

However, I am getting this error

Uncaught Database_Exception [ 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 'p, pets_activities pa...

I've never done a cross table delete before, so I'm inexperienced and stuck for now!

What am I doing wrong?

6 comments

@Kalesh Kaladharan 2017-10-23 08:38:31

To anyone reading this in 2017, this is how I've done something similar.

DELETE pets, pets_activities FROM pets inner join pets_activities
on pets_activities.id = pets.id WHERE pets.`order` > :order AND 
pets.`pet_id` = :pet_id

Generally, to delete rows from multiple tables, the syntax I follow is given below. The solution is based on an assumption that there is some relation between the two tables.

DELETE table1, table2 FROM table1 inner join table2 on table2.id = table1.id
WHERE [conditions]

@cadman 2012-07-11 21:56:33

Use a JOIN in the DELETE statement.

DELETE p, pa
      FROM pets p
      JOIN pets_activities pa ON pa.id = p.pet_id
     WHERE p.order > :order
       AND p.pet_id = :pet_id

Alternatively you can use...

DELETE pa
      FROM pets_activities pa
      JOIN pets p ON pa.id = p.pet_id
 WHERE p.order > :order
   AND p.pet_id = :pet_id

...to delete only from pets_activities

See http://dev.mysql.com/doc/refman/5.0/en/delete.html

For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS, NOT EXISTS, IN, NOT IN, etc. But the one above where you specify from which tables to delete with an alias before the FROM clause can get you out of a few pretty tight spots more easily. I tend to reach out to an EXISTS in 99% of the cases and then there is the 1% where this MySQL syntax takes the day.

@techouse 2012-07-30 21:29:41

I tried this "delete all in 1 query" with joining 6 large tables (everyone about ~15k rows) and the query took 155 seconds to delete 63 rows in 6 tables :O

@Simon Christian 2013-03-06 12:14:03

@cadman This is the real right answer; there may be arguments against using it, but it's very useful on occasion

@Erick Robertson 2014-01-09 02:47:35

+1 I agree that this in the real right answer, since the question was not "should you" but "how to". However, I would be interested in hearing about the 1% because I can't think of a single situation where this would be preferred.

@Paul Draper 2014-05-22 20:07:32

@techouse, did you join and filter on indices? 15k x 15k x 15k x 15k 15k x 15k is 11 million. Did a SELECT take similarly long?

@Lexib0y 2015-11-13 12:02:56

You can also use LEFT JOIN, which is usefull if the second table had no matching entries, else nothing will be deleted.

@zhuguowei 2016-06-18 02:23:57

Thanks, but I met another question when execute delete in safe-update mode, please see stackoverflow.com/questions/37892657/…

@RN Kushwaha 2015-02-20 05:08:33

Use this

DELETE FROM `articles`, `comments` 
USING `articles`,`comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

or

DELETE `articles`, `comments` 
FROM `articles`, `comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

@Gene Bo 2015-10-05 19:05:04

Nice - seems better & easier than using JOIN. It's the best solution I see here .. thanks!

@Mavelo 2017-01-09 15:08:24

Found a good reference for using this and a few other options at mysqltutorial.org/mysql-delete-statement.aspx

@General Omosco 2018-12-25 14:13:26

Cool using "USING"

@paxdiablo 2010-07-26 03:17:56

Since this appears to be a simple parent/child relationship between pets and pets_activities, you would be better off creating your foreign key constraint with a deleting cascade.

That way, when a pets row is deleted, the pets_activities rows associated with it are automatically deleted as well.

Then your query becomes a simple:

delete from `pets`
    where `order` > :order
      and `pet_id` = :pet_id

@Erick Robertson 2010-07-26 03:18:57

Delete cascades are inviting horrible accidents.

@alex 2010-07-26 03:20:09

Thanks Paxdiablo, I'm not sure how to do this yet in MySQL but I'll consider it.

@paxdiablo 2010-07-26 03:24:28

@Erick, provided you've set up referential integrity, cascading deletes can cause no more trouble than delete on its own. We already know that pa is a proper child of p due to the id/pet_id mapping.

@Erick Robertson 2010-07-26 03:26:42

Cascading deletes just means that things will be deleted which you aren't explicitly saying to delete. That's always dangerous.

@paxdiablo 2010-07-26 03:42:59

You are explicitly saying you want to delete them. Just not at the time of delete. You don't explicitly say you want triggers fired at the time of insertion either but that doesn't make them dangerous.

@Erick Robertson 2010-07-26 03:48:05

I disagree. I prefer to use a database as a database, and not as an execution platform.

@OMG Ponies 2010-07-26 03:50:05

I agree with Erick on this one - can be hazardous. Somewhat related, SQL Server 2005 doesn't handle cascade deletes if on the same table. The safest means is to process data in relational order

@paxdiablo 2010-07-26 04:03:08

Well, you guys have your own thoughts but it seems like you're discounting a lot of the power of DBMS'. Cascading deletes are as much a part of data management as triggers, stored procedures or constraints and they're only dangerous if you don't know what you're doing. Still, I won't argue the point further, we'll just have to agree to disagree.

@OMG Ponies 2010-07-26 04:25:30

Getting data in isn't the issue - it's situations where you have to reconstruct data that wasn't supposed to be deleted which is a real hassle. Only Oracle has Flashback, a temporal compliment to it's database for inline reconstituting accidentally deleted data. SQL Server has snapshots, but it's nowhere near as slick as Flashback. That's why Erick, myself, and numerous others I've worked with prefer explicit deletion. Like the case with SQL Server, it's not always a cut'n'dried operation either, or hasn't been in the past.

@paxdiablo 2010-07-26 08:32:56

Again, not an issue if it's set up correctly - the problems you mention are no different to deleting rows accidentally without cascade. Seriously, if someone doesn't know how to design and implement databases, they have no business trying to do it.

@Erick Robertson 2010-07-26 11:26:33

It probably comes as no surprise, but I also do not use triggers, stored procedures, or constraints. I prefer to put this kind of logic in my execution platform and use my database for storing and retrieving data only. This doesn't mean I don't know how to use or understand these features, however.

@paxdiablo 2010-07-26 23:46:18

Erick, now you've piqued my interest. How do you ensure data integrity within the database without constraints?

@james.garriss 2013-08-24 18:17:36

@Erick said, "I also do not use triggers, stored procedures, or constraints." Ah, you use Excel. :-)

@Erick Robertson 2013-10-16 17:22:09

I just want to follow up on this. I have changed my position on deleting cascades in this situation. I have been a part of a new SQL environment which used them, and used them well and they were very organized. In this system it worked very well to our advantage to have these cascades in place. It certainly prevented orphaned data and was not dangerous. The problem is that everyone working with the database needs to understand how to use them safely. But there are always risks when junior devs are making database changes unsupervised.

@Brandon Horsley 2010-07-26 03:51:28

I don't have a mysql database to test on at the moment, but have you tried specifying what to delete prior to the from clause? For example:

DELETE p, pa FROM `pets` p,
        `pets_activities` pa
  WHERE p.`order` > :order
    AND p.`pet_id` = :pet_id
    AND pa.`id` = p.`pet_id`

I think the syntax you used is limited to newer versions of mysql.

@alex 2010-07-26 04:43:46

That query executed successfully, however, it didn't delete any rows (but I believe it should have).

@Andre Gallo 2010-07-26 03:13:41

The syntax looks right to me ... try to change it to use INNER JOIN ...

Have a look at this: http://www.electrictoolbox.com/article/mysql/cross-table-delete/

@mycroes 2014-05-14 06:49:44

Too bad you didn't include the actual solution, because the link is correct!

Related Questions

Sponsored Content

42 Answered Questions

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

33 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

3 Answered Questions

[SOLVED] How to get the max of two values in MySQL?

  • 2009-10-14 11:25:39
  • Mask
  • 105654 View
  • 263 Score
  • 3 Answer
  • Tags:   mysql max

7 Answered Questions

[SOLVED] Adding multiple columns AFTER a specific column in MySQL

  • 2013-07-09 06:22:00
  • Koala
  • 486593 View
  • 308 Score
  • 7 Answer
  • Tags:   mysql ddl

28 Answered Questions

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

33 Answered Questions

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

5 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

10 Answered Questions

[SOLVED] How to remove constraints from my MySQL table?

15 Answered Questions

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

2 Answered Questions

[SOLVED] Cast from VARCHAR to INT - MySQL

  • 2012-08-26 01:26:30
  • Lenin Raj Rajasekaran
  • 536308 View
  • 227 Score
  • 2 Answer
  • Tags:   mysql sql

Sponsored Content