Question

求助一条SQL语句，关于DELETE使用JOIN的

执行时报错#1064 - You have an error in your SQL syntax
SQL语句如下
DELETE FROM c_works Works JOIN c_works_attach Works_attach ON Works_attach.wid=Works.id JOIN c_works_image Works_image ON Works_image.wid=Works.id WHERE ( Works.id = 1 )

Answer 1

首先你必须得指明你删除哪个表中的数据
DELETE Works
FROM c_works Works JOIN c_works_attach Works_attach ON Works_attach.wid=Works.id JOIN c_works_image Works_image ON Works_image.wid=Works.id WHERE ( Works.id = 1 )
其次，sql server的话，得分开写，不能写到一起，你可以写一个tranction,例如
begin transaction;

declare @deletedIds table ( id int );

DELETE Works
output deleted.id into @deletedIds
FROM c_works Works JOIN c_works_attach
Works_attach ON Works_attach.wid=Works.id JOIN c_works_image Works_image
ON Works_image.wid=Works.id WHERE ( Works.id = 1 )

delete Works_attach
from c_works_attach as Works_attach

join @deletedIds d
on d.id = Works_attach.id;

delete Works_image

from c_works_image Works_image

join @deletedIds d
on d.id = Works_image.id;
commit transaction;

Wish it Helps

Answer 2

这种语法在SQLSERVER中没问题，其他数据库没试


DELETE FROM Works from c_works Works JOIN c_works_attach Works_attach ON Works_attach.wid=Works.id JOIN c_works_image Works_image ON Works_image.wid=Works.id WHERE ( Works.id = 1 )

Answer 3

delete from 不能用 join

追问

请问有什么办法能一次性删除相关联的三个表的数据吗？