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`)
);

4 comments

@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 t1.id = t2.id;

The second syntax is slightly different:

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

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

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

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: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Related Questions

Sponsored Content

3 Answered Questions

JOIN Query In MYSQL and PHP

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

9 Answered Questions

[SOLVED] Add Foreign Key to existing table

  • 2012-04-05 12:02:37
  • frgtv10
  • 534279 View
  • 269 Score
  • 9 Answer
  • Tags:   mysql foreign-keys

1 Answered Questions

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

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

15 Answered Questions

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

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
  • 13719 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