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,500 points
2,139 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,740 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,740 points
640 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 in PHP by narikkadan
• 11,280 points
14 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 in PHP by narikkadan
• 11,280 points
68 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 in PHP by narikkadan
• 11,280 points
22 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,740 points
14,722 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,740 points
1,663 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,740 points
1,378 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,740 points
19,990 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,740 points
2,000 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,740 points
518 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP