By AdRock

2009-04-09 14:50:43 8 Comments

I'm trying to use SQL to delete multiple rows from multiple tables that are joined together.

Table A is joined to Table B Table B is joined to Table C

I want to delete all rows in table B & C that correspond to a row in Table A

CREATE TABLE `boards` (
  `boardid` int(2) NOT NULL AUTO_INCREMENT,
  `boardname` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`boardid`)

-- --------------------------------------------------------

-- Table structure for table `messages`

CREATE TABLE `messages` (
  `messageid` int(6) NOT NULL AUTO_INCREMENT,
  `boardid` int(2) NOT NULL DEFAULT '0',
  `topicid` int(4) NOT NULL DEFAULT '0',
  `message` text NOT NULL,
  `author` varchar(255) NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL,
  PRIMARY KEY  (`messageid`)

-- --------------------------------------------------------

-- Table structure for table `topics`

CREATE TABLE `topics` (
  `topicid` int(4) NOT NULL AUTO_INCREMENT,
  `boardid` int(2) NOT NULL DEFAULT '0',
  `topicname` varchar(255) NOT NULL DEFAULT '',
  `author` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`topicid`)


@Peter 2009-04-09 15:00:11

You could either just check for presence

delete from topics where boardid in (select boardid from boards)
delete from messages where boardid in (select boardid from boards)

but this would only make sense if this behaviour should not always apply. When the behaviour should always apply, implement foreign keys with delete on cascade

explained on a zillion sites, in your helpfiles and here

@Deepak 2014-02-24 10:12:27

Deleting rows from multiple tables can be done in two ways :

  • Delete rows from one table, determining which rows to delete by referring to another table
  • Delete rows from multiple tables with a single statement

Multiple-table DELETE statements can be written in two formats. The following example demonstrates one syntax, for a query that deletes rows from a table t1 where the id values match those in a table t2:

DELETE t1 FROM t1, t2 WHERE =;

The second syntax is slightly different:


To delete the matching records from both tables, the statements are:

DELETE t1, t2 FROM t1, t2 WHERE =;
DELETE FROM t1, t2 USING t1, t2 WHERE =;

The ORDER BY and LIMIT clauses normally supported by UPDATE and DELETE aren’t allowed when these statements are used for multiple-table operations.

@Chad Birch 2009-04-09 14:58:33

Well, if you had used InnoDB tables, you could set up a cascading delete with foreign keys that would do it all automatically. But if you have some reason for using MyISAM, You just use a multiple-table DELETE:

DELETE FROM boards, topics, messages
USING boards INNER JOIN topics INNER JOIN messages
WHERE boards.boardid = $boardid
    AND topics.boardid = boards.boardid
    AND messages.boardid = boards.boardid;

@pingu 2013-11-08 12:01:11

Why "USING" and not "ON"?

@nickanor 2014-03-02 17:30:44

+1 here thanks..

@vikram 2017-11-08 14:47:21

what if the table has more than 50M records?

@Bernd Ott 2009-04-09 14:54:11

this can be done by your db-system if you are using foreign keys with "on delete cascade".

Take a look here:

Related Questions

Sponsored Content

15 Answered Questions

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

10 Answered Questions

[SOLVED] Add Foreign Key to existing table

  • 2012-04-05 12:02:37
  • frgtv10
  • 558727 View
  • 280 Score
  • 10 Answer
  • Tags:   mysql foreign-keys

3 Answered Questions


  • 2015-08-07 19:22:48
  • Imran Khan
  • 249 View
  • 2 Score
  • 3 Answer
  • Tags:   php mysql

1 Answered Questions

[SOLVED] Unable to relate two MySQL tables (foreign keys)

  • 2010-06-13 13:15:29
  • kapeels
  • 843 View
  • 3 Score
  • 1 Answer
  • Tags:   php mysql

14 Answered Questions

2 Answered Questions

[SOLVED] SQL create table and set auto increment value without Alter table

  • 2015-04-07 08:16:23
  • Blue Fishy
  • 14396 View
  • 3 Score
  • 2 Answer
  • Tags:   mysql create-table

3 Answered Questions

MySQL multiple Sending Data states

2 Answered Questions

[SOLVED] how to fetch menu from database with its subcategory

1 Answered Questions

[SOLVED] mysql relation slow

1 Answered Questions

[SOLVED] Optimize multiple JOINs in MySQL

Sponsored Content