delete from 和 left Join连在一起怎么用

Question

SQL: DELETE FROM task_news as a LEFT JOIN temp_new as b ON a.ID=b.ID WHERE 1 and b.UserID>0

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a LEFT JOIN temp_new as b ON a.ID=b.ID WHERE 1 and b.UserID>0' at line 1

这个不能连在一起用吗，为什么报这个错，谁能解答一下，非常感谢

Answer 1

可以一起用，参见
http://www.mysqltutorial.org/...

你的问题在于，没指定删除a表还是b表数据，MySQL很懵逼。

DELETE
    a
FROM
    task_news a
LEFT JOIN
    temp_new b
ON
    a.ID = b.ID
WHERE
    b.UserID > 0

Answer 2

这样写

DELETE FROM task_news a where exists(select 1 from temp_new b where a.ID=b.ID and b.UserID>0)

Answer 3

你的问题在于delete中 别名的问题： delete 别名 from 表名 别名 where 条件