delete rows from multiple tables

Question

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

Solution 1

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;

Solution 2

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

Solution 3

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

Solution 4

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.

View more solutions

Comments

[pingu]

Why "USING" and not "ON"?

[vikram]

what if the table has more than 50M records?