I have a table where I would like to delete rows with INNER JOIN where the WHERE clause is based on a value in another table. The primary_key in Table 2 is a foreign_key in Table 1.
Table 1
table_1_id | customer_id | table_2_id
-----------------------------------------
1 | 5 | 1
2 | 5 | 2
3 | 5 | 3
Table 2
table_2_id | value
-----------------------
1 | 0
2 | 0
3 | 1
I want to delete rows from Table 1 where the value in Table 2 is equal to 0. So in this example the DELETE statement should delete the first two rows in Table 1, since I use INNER JOIN to connect the two tables.
I tried this but it doesnt exactly do what I want. This statement always deletes all the rows in Table 1 instead of the first two only.
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'
You are thinking too complicatedly. You want to delete from table 1 where a condition is met:
delete from t1 where customer_id = 5 and t2_id in (select t2_id from t2 where value = 0);
I cannot replicate this behaviour:
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)
See. Works just fine.