How to Delete from Multiple Tables in MySQL

How to delete from multiple tables in MySQL?

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

For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS, NOT EXISTS, IN, NOT IN and 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.

Mysql - delete from multiple tables with one query

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Then deleting the record from parent table removes the records from child tables.

Check this link : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Delete from multiple tables in one statement

Yes, changing the join on table2 to left join would do what you want. Rows in table1 that belong to the list and to table3 will all get deleted, regardless whether they also exist in table2. In the meantime, possible matching rows in table2 will be deleted too.

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
inner join table3 t3 on t1.id = t3.table1_id
where t1.id in (?, ?, ?, ?);

I would suggest rewriting the join on table3 to an exists condition instead. This makes the intent of the query more obvious, and might perform better, especially if you have an index on table3(table1_id):

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
where 
    t1.id in (?, ?, ?, ?)
    and exists (select 1 from table3 t3 where t3.table1_id = t1.id)

How to delete from multiple tables with the same column in mysql?

Use a JOIN with a WHERE, it is much clearer.

DELETE country , city 
FROM country JOIN city 
ON city.country_id = country.id 
WHERE  country.visible = 0

ON your sample data the kkk row with visible=0 wont be deleted since it has an id of 4 which doenst exist in the city table so it won`t be picked up in the join.

MySQL delete row from multiple tables

Yes, that is correct. It works fine here:

CREATE TABLE table1 (id int, username nvarchar(30));
CREATE TABLE table2 (id int);
CREATE TABLE table3 (id int);
CREATE TABLE table4 (id int);

INSERT INTO table1 VALUES (1, 'Foo'),(2, 'Bar');
INSERT INTO table2 VALUES (1),(2);
INSERT INTO table3 VALUES (1),(2);
INSERT INTO table4 VALUES (1),(2);

SELECT COUNT(*) FROM table1;
2
SELECT COUNT(*) FROM table2;
2
SELECT COUNT(*) FROM table3;
2
SELECT COUNT(*) FROM table4;
2

DELETE t1, t2, t3, t4 FROM
  table1 as t1
  INNER JOIN  table2 as t2 on t1.id = t2.id
  INNER JOIN  table3 as t3 on t1.id=t3.id
  INNER JOIN  table4 as t4 on t1.id=t4.id
  WHERE  t1.username='Foo' AND t1.id='1';

SELECT COUNT(*) FROM table1;
1
SELECT COUNT(*) FROM table2;
1
SELECT COUNT(*) FROM table3;
1
SELECT COUNT(*) FROM table4;
1

If it's not working for you, perhaps you can modify this example to show what problem you are having.

Mysql delete multiple tables with left join in one statement caused FK violation

From the manual:

If you use a multiple-table DELETE statement involving InnoDB tables
for which there are foreign key constraints, the MySQL optimizer might
process tables in an order that differs from that of their
parent/child relationship. In this case, the statement fails and rolls
back. Instead, you should delete from a single table and rely on the
ON DELETE capabilities that InnoDB provides to cause the other tables
to be modified accordingly.

As the manual (and @Barmar) says, the way to work around this is to add ON DELETE CASCADE to your constraints e.g. in FullTimeStudent and StudentDetail use:

FOREIGN KEY (studentID) REFERENCES Student(id) ON DELETE CASCADE

Delete from two tables in one query

Can't you just separate them by a semicolon?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

OR

Just use INNER JOIN as below

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

Deleting rows from multiple tables in MySQL

DELETE projects, images 
FROM projects, images 
WHERE projects.p_id = ? 
AND projects.p_id = images.p_id;

---

Related Topics