Delete查询用于在sql server和c#中删除多个表中的数据
本文关键字:删除 数据 用于 查询 sql server Delete | 更新日期: 2023-09-27 18:13:49
我有三个表mainCategory
, subCategory
和products
。我在gridview中显示数据。我想从管理面板中删除主类别,但到那时,我还需要从子类别表和产品中删除数据,这些数据由主类别表组成。
这些是表中的列:
-
mainCategory
mainCatID (primary key) mainCatName
-
subCategory
subCatId (primary key) mainCatId subCatName mainCatName
-
products
productId (primary key) subCatId subCatName productName productImage
在SqlCommand
对象中写什么SQL ?Gridview是用函数绑定代码的。
像这样?把它分成三个单独的语句似乎是最简单的。
DELETE FROM Products WHERE SubCatId IN (SELECT SubCatID FROM SubCategory WHERE MainCatId = @mainCatId);
DELETE FROM SubCategory WHERE MainCatId = @mainCatId;
DELETE FROM MainCategory WHERE MainCatId = @mainCatId;
这里有两种可能性。首先,在这三个表之间的关系上启用了级联删除。如果是这种情况,那么就像下面这样简单(假设没有循环/多个级联路径):
DELETE
FROM mainCategory WHERE mainCatID = @someId
如果你没有启用级联删除,那么它将是一个三步的过程:
-- remove the products
DELETE a
FROM products as a
JOIN subcategory as b
ON a.subCatId = b.subCatId
AND b.mainCatID = @someId
-- remove the subcategories
DELETE
FROM subcategory WHERE mainCatID = @someId
-- remove main categories
DELETE
FROM products WHERE mainCatID = @someId