一个关于SQL多表删除的问题

来源：百度知道 编辑：UC知道 时间：2024/06/18 15:10:39
有3个表: a ,b,c
a中有: id_a , name_a (1,上海大众 等厂商)
b中有: id_b , a_id , name_b (1,1,斯柯达...等等品牌)
c中有: id_c , b_id , name_c (1,1,明锐1.8T 等很多型号)

现在想要删除比如 上海大众,然后同时就要删除 上海大众的所有品牌和所有型号 ;

比如:
厂商: 品牌 型号
上海大众 斯柯达 明锐8.0转子引擎
上海大众 斯柯达 明锐1.8T
上海大众 达可思 捷达Ci
上海大众 达可思 桑塔纳kk
四川长虹 反恐精英 格洛克18
四川长虹 反恐精英 沙漠之鹰
四川长虹 反恐精英 神州7号

请问SQL该怎么写呢??

delete from a where exists (select id from b,c where a.id=b.id and b.id=c.id)

DELETE * FROM (a INNER JOIN b ON a.id_a = b.a_id) INNER JOIN c ON b.id_b = c.b_id

DECLARE @Name AS nVarChar(50)
SET @Name = '上海大众'

DECLARE @ID_a AS INT
DECLARE @Err AS INT
SELECT @ID_a = id_a FROM a WHERE name_a = @Name

--开始删除
BEGIN TRAN
DELETE FROM c WHERE b_id IN (SELECT id_b FROM b WHERE a_id = @ID_a)
DELETE FROM b WHERE a_id = @ID_a
DELETE FROM a WHERE id_a = @ID_a
--无错提交，有错回滚
SET @Err = @@Error
IF @Err = 0
COMMIT
ELSE
ROLLBACK