基于另一张表中的值删除MySQL行

Question

我有一个表格，想通过INNER JOIN删除其中基于另一个表中某个值的WHERE子句的行。表2中的primary_key是表1中的foreign_key。

表1

table_1_id  |  customer_id  |  table_2_id
-----------------------------------------
     1      |      5        |       1 
     2      |      5        |       2 
     3      |      5        |       3 

表格2

table_2_id   |    value
-----------------------
   1         |      0 
   2         |      0 
   3         |      1 

我想要从表1中删除在表2中value等于0的行。因此，在这个例子中，DELETE语句应该删除表1中的前两行，因为我使用INNER JOIN连接了这两个表。

我尝试了这个方法，但它并没有完全做到我想要的。这个语句总是删除表1中的所有行，而不仅仅是前两行。

DELETE t1
FROM Table1 t1
INNER JOIN Table2 t2 ON (t1.table_2_id = t2.table_2_id) 
WHERE t1.customer_id = '5' 
AND t2.value = '0'

- SC92

---

尝试将你的where子句放在括号中，看看会发生什么。 - Sean Konig

它在 Fiddle 中运行正常。 - M Khalid Junaid

我无法复制这种行为。 - Strawberry

Answer 1

你想得太复杂了。你想要在满足某个条件的情况下从表1中删除数据：

delete from t1 where customer_id = 5 and t2_id in (select t2_id from t2 where value = 0);

- Thorsten Kettner

---

我应该直接放答案而不是评论的；-) 做得好 - Sean Konig

OP并没有想得太复杂。OP的解决方案完全适用于所描述的上下文。无论问题是什么，它都存在于其他地方。 - Strawberry

@Strawberry：哈哈，你抓住我了。我必须承认，我没有仔细看OP的删除语句，因为连接表对于这样的任务只会让事情变得更加复杂。然而，你是正确的，OP的删除语句也应该可以工作。也许在表2中有一些OP不知道的记录，可以通过单独运行我的子查询来轻松检查。 - Thorsten Kettner

...或者将OP的查询作为SELECT运行 - Strawberry

我不知道我做错了什么，但是它似乎可以用这个方法解决。谢谢。我真的无法弄清楚为什么我的查询不起作用，因为我不太清楚逻辑上有什么问题。但现在它可以工作了，谢谢。 - SC92

Answer 2

我无法复制这种行为：

DROP TABLE IF EXISTS table1;

CREATE TABLE table1
(table_1_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,customer_id INT NOT NULL
,table_2_id INT NOT NULL
);

INSERT INTO table1 VALUES
(1,5,1),
(2,5,2),
(3,5,3);

DROP TABLE IF EXISTS table2;

CREATE TABLE table2
(table_2_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,value TINYINT NOT NULL
);

INSERT INTO table2 VALUES
(1,0),
(2,0),
(3,1);

SELECT * FROM table1;
+------------+-------------+------------+
| table_1_id | customer_id | table_2_id |
+------------+-------------+------------+
|          1 |           5 |          1 |
|          2 |           5 |          2 |
|          3 |           5 |          3 |
+------------+-------------+------------+
3 rows in set (0.01 sec)

SELECT * FROM table2;
+------------+-------+
| table_2_id | value |
+------------+-------+
|          1 |     0 |
|          2 |     0 |
|          3 |     1 |
+------------+-------+
3 rows in set (0.00 sec)

DELETE t1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.table_2_id = t2.table_2_id
WHERE t1.customer_id = 5
AND t2.value = 0
;

SELECT * FROM table1;
+------------+-------------+------------+
| table_1_id | customer_id | table_2_id |
+------------+-------------+------------+
|          3 |           5 |          3 |
+------------+-------------+------------+
1 row in set (0.00 sec)

看，运行得很好。

- Strawberry

---

确实很奇怪。我甚至在我的MySQL数据库中尝试了它，但没有结果。一切看起来都和我的一样。 - SC92

你的意思是说，如果你将上面的内容复制粘贴到你的系统中，它就不能工作？请原谅我的疑惑。 - Strawberry