从多个表中删除行
我正在尝试使用 SQL 从多个表中删除多行 联合在一起。
表 A 连接到表 B 表 B 已连接到表 C
我想删除表 B 和表 C 中的所有行 C 对应于表 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`)
);
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`)
);
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
好吧,如果您使用过 InnoDB 表,您可以设置 使用外键进行级联删除,这一切都会自动完成。 但是,如果您出于某种原因使用 MyISAM,您只需使用 多表删除:
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:
如果您使用带有“删除级联”的外键,则可以通过您的数据库系统来完成此操作。
看看这里:
http://dev.mysql.com/doc /refman/5.1/en/innodb-foreign-key-constraints.html
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
您可以只检查是否存在,
但这只有在这种行为不总是适用时才有意义。 当该行为应始终适用时,请
在无数站点、帮助文件和
You could either just check for presence
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
从多个表中删除行可以通过两种方式完成:
table
行 多表 DELETE 语句可以用两种格式编写。 以下示例演示了一种语法,该查询从表 t1 中删除 id 值与表 t2 中的行相匹配的行:
第二个语法略有不同:
要从两个表中删除匹配的记录,语句为
:当这些语句用于多表操作时,通常由 UPDATE 和 DELETE 支持的 BY 和 LIMIT 子句是不允许的。
Deleting rows from multiple tables can be done in two ways :
table
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:
The second syntax is slightly different:
To delete the matching records from both tables, the statements are:
The ORDER BY and LIMIT clauses normally supported by UPDATE and DELETE aren’t allowed when these statements are used for multiple-table operations.