如何使用单个查询删除多个记录 [英] how to delete multiple record using single query

问题描述

我正在使用asp.net，C#，VS 2005，sql server 2005 ..

使用单个查询从与外键相关的不同表中删除多个记录的命令是什么?

即

我有2张桌子:

以column1作为主键的table1

table2的column1引用了table1的column1 ...

如果发生故障，还应该执行回滚操作.

有人可以帮我吗...

问候
karan

I am using asp.net,C#,VS 2005,sql server 2005..

What is the command to delete multiple records from different tables which are related using foreign key using single query .?

i.e.,

I have 2 tables:

table1 with column1 as primary key

table2 with column1 which refers to column1 of table1...

Also there should be a rollback operation to be done in case of failure..

Can anybody help me please...

regards
karan

推荐答案

我需要使用存储过程而不是查询字符串
您可以在同一存储过程中编写两个delete语句.
并传递参数

另一个选项是您可以在sql server中设置它

在表设计中，选择前键关系，然后选择要删除的关系

有一个名为插入和更新规范"的属性
选择删除规则=级联
因此，您只需要删除主键行，它将删除相应的foreginkey行

I thing you need to use stored procedure instead of query string
you can write both delete statement in the same stored procedure
and pass the parameter

other option is you can set this in sql server

In the table design select the foreginkey relationships select the relationship you want to delete

there is a property called Insert and Update Specification
Select the Delete Rule =Cascade
so you just need to delete primarykey row it will delete the corresponding foreginkey rows

正如abhinav在解决方案1中所说的那样，层叠是件好事，您需要编写一个查询来删除并实现也.
正如VysakhMenon所说的，解决方案3必须是首选的存储过程.

使用您的连接对象控制交易. (con.Begintrancation).

As abhinav said in Solution 1 cascade is good thing and u need to write one query to delete and realted will be delated too.
as VysakhMenon said solution 3 u must stored procedure thats preferred way.

Control Transaction using your connection object. (con.Begintrancation).

(SQL Server 2005)
参见以下脚本:

(SQL server 2005)
See below script:

ALTER TABLE Table2
    ADD CONSTRAINT FK_Something FOREIGN KEY (PK_Of_Table1) REFERENCES Table1(PK_Of_Table1)
        ON DELETE CASCADE
        ON UPDATE CASCADE ;

在这种情况下，如果您从表1中删除某些内容，这将导致表2中所有与之相关的记录也被删除.
为了安全:

In this case if you delete something from Table1 this will cause all records in Table2 which are related to be deleted aswell.
For safety:

BEGIN TRAN You_name_it
       DELETE FROM Table1 Where blah_blah != blah_blah_blah

然后

COMMIT TRANSACTION You_name_it;

如果满意或失败:

if satisfied or in case of failure:

ROLLBACK TRAN You_name_it;

注意1:回退提交顺序可能不对，您应该确认.
注意2:这与c#有什么关系?我不知道.

Note 1: I might be wrong about rolling back order of commit etc you should confirm it.
Note 2: What does this have to do with c#? I dont know.

这篇关于如何使用单个查询删除多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！