从多个表中删除行 [英] delete rows from multiple tables

问题描述

我正在尝试使用SQL从多个表中删除多行 在一起.

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

表A连接到表B 表B联接到表C

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

我想删除表B&中的所有行；与表A中的一行相对应的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`)
);

推荐答案

好吧，如果您使用过InnoDB表，则可以设置 multiple-table删除:

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;

这篇关于从多个表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！