MySQL 使用 Join 删除记录不起作用

Question

我正在尝试使用 JOIN 删除记录但没有用，所以我尝试使用 IN 它也没有用。但是，如果我将 DELETE 更改为 SELECT 它工作正常。

工作

SELECT * FROM T1 
     WHERE T1.ID IN (
     SELECT T1.ID FROM T1 
     JOIN T2 ON T2.PID = T1.ID WHERE T2.Email LIKE '%mitul%')

不工作

DELETE FROM T1 
     WHERE T1.ID IN (
     SELECT T1.ID FROM T1 
     JOIN T2 ON T2.PID = T1.ID WHERE T2.Email LIKE '%mitul%')

DELETE FROM T1 
     JOIN T2 ON T2.PID = T1.ID WHERE T2.Email LIKE '%mitul%'

Answer 1

您可以在DELETE 语句之后提及表名或表别名:

DELETE T1
FROM T1 
JOIN T2 ON T2.PID = T1.ID 
WHERE T2.Email LIKE '%mitul%'

请在 db<>fiddle 上找到演示

如果您想从两个表中删除匹配的条目，请在 DELETE 语句之后使用 T1, T2。

DELETE T1, T2
FROM T1 
JOIN T2 ON T2.PID = T1.ID 
WHERE T2.Email LIKE '%mitul%'

Demo for the same