How to delete from multiple tables in MySQL

0 votes

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

What am I doing wrong?

Aug 26, 2020 in PHP by kartik
• 37,510 points
3,404 views

1 answer to this question.

0 votes

Hello @kartik,

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

Hope it helps!!
Thank You!

answered Aug 26, 2020 by Niroj
• 82,880 points

Related Questions In PHP

0 votes
1 answer

How to delete a certain row from mysql table with same column values?

Hello @kartik, Add a limit to the delete query delete from orders where ...READ MORE

answered Aug 26, 2020 in PHP by Niroj
• 82,880 points
1,417 views
0 votes
0 answers

How to change maximum viewable tables from 250 to anything in PhpMyadmin?

I wanted to increase my list of ...READ MORE

Jun 10, 2022 in PHP by narikkadan
• 63,420 points
486 views
0 votes
0 answers

How to retrieve images from MySQL database and display in an html tag

I created a table using the  BLOB column ...READ MORE

Jun 19, 2022 in PHP by narikkadan
• 63,420 points
1,225 views
0 votes
0 answers

How to retrieve data from multiple tables using a PHP form?

I want to retrieve data from multiple ...READ MORE

Jun 25, 2022 in PHP by narikkadan
• 63,420 points
1,513 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,880 points
21,708 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,880 points
2,630 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,880 points
2,486 views
+1 vote
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,880 points
41,156 views
0 votes
1 answer

How to concatenate text from multiple rows into a single text string in SQL server?

Hello @kartik, Use COALESCE: DECLARE @Names VARCHAR(8000) SELECT @Names = ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,880 points
2,754 views
0 votes
1 answer

How to get all columns' names for all the tables in MySQL?

Hello @kartik, Try this: select * from information_schema.columns where table_schema ...READ MORE

answered Aug 19, 2020 in PHP by Niroj
• 82,880 points
2,065 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP