时间戳

首页 > 解决方案 > 根据日期从多个表中删除

mysql - 根据日期从多个表中删除

问题描述

我正在尝试从 3 个表中删除具有相同 ID 的记录。这些表是这样的：

Table1
+----------+----------------+-----------+---------+----------+
| commonid | creation_date  | column 1  | column 2| column 3 |
+----------+----------------+-----------+---------+----------+

Table2
+----------+---------+----------+---------+----------+
| commonid | column 1| column 2 | column 3| column 4 |
+----------+---------+----------+---------+----------+

Table3
+----------+---------+----------+---------+----------+
| commonid | column 1| column 2 | column 3| column 4 |
+----------+---------+----------+---------+----------+

所以要选择我正在使用的所有数据

SELECT * FROM table1
INNER JOIN table2 
ON table1.commonid = table2.commonid 
INNER JOIN table3
ON table1.commonid = table3.commonid
WHERE creation_date = '2018-08-01 04:13:50'

这将返回 6 行。要删除我会尝试：

DELETE table1 FROM table1
INNER JOIN table2 
ON table1.commonid = table2.commonid 
INNER JOIN table3
ON table1.commonid = table3.commonid
WHERE creation_date = '2018-08-01 04:13:50'

它返回受影响的 1 行，我本来期望 6。当我再次运行第一个查询时，我得到 0 个结果。表 3 的总行数不受影响。

如何commonid从每个表中删除相同的行？

标签： mysqlinner-joinsql-delete

解决方案

您需要将所有三个表指定为删除目标：

DELETE t1, t2, t3
FROM table1 t1
INNER JOIN table2 t2
    ON t1.commonid = t2.commonid 
INNER JOIN table3 t3
    ON t1.commonid = t3.commonid
WHERE
    creation_date = '2018-08-01 04:13:50';

目前，您只是告诉 MySQL 从table1.

推荐阅读