Member Avatar for jlego

I'm trying to do a delete on two tables at once.

sucessfully does it with update,

UPDATE table1 INNER JOIN table2 ON table2.table1_id = table1.ID SET table1.whatever = Whatever, table2.whateveer = Whatever

is there a way to do this with the DELETE function

DELETE table1 INNER JOIN table2 ON table2.table1_id = table1.id WHERE table1.ID = 1

doesn't work

thanks in advanced.

  • 2 Contributors
  • 2 Replies
  • 104 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jlego

Dani AI

Generated

As already pointed out, MySQL can remove rows from more than one table in a single statement. The practical part is not just syntax but doing it safely: preview what will be removed, avoid breaking referential integrity, and keep transactions and locks under control on production data. See the MySQL manual for the DELETE statement for exact syntax and rules (MySQL DELETE syntax).

A safe workflow: first run targeted SELECT/COUNT queries to confirm the affected rows. Then perform deletes inside a transaction so you can roll back if something is wrong. If there are no FK cascades, delete child rows before the parent to avoid orphans. Example pattern:

START TRANSACTION;
DELETE FROM child_table WHERE parent_id = 1;
DELETE FROM parent_table WHERE id = 1;
COMMIT;

If deleting large numbers of rows, avoid one giant transaction. Delete in chunks to reduce locks and log growth:

DELETE FROM large_child_table
WHERE parent_id = 1
LIMIT 10000;

repeat until no rows are deleted.

If you rely on automatic cleanup, confirm InnoDB foreign-key behavior and ON DELETE CASCADE settings before relying on them (). Use EXPLAIN on your DELETE to check the join/scan plan, and always have backups or run in a non-production snapshot first. This complements ’s syntax notes while focusing on correctness and operational safety.

Recommended Answers

All 2 Replies

Member Avatar for darkagn

When deleteing from multiple tables, you need to specify what is being deleted. There are two available syntaxes:

DELETE table1, table2 
FROM table1 INNER JOIN table2 ON table1.id = table2.id 
WHERE table1.id = 1

or:

DELETE FROM table1, table2 
USING table1 INNER JOIN table2
WHERE table1.id=table2.id AND table1.id = 1

Hope this helps.

Member Avatar for jlego

thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.