如何在单个查询中从不同表中删除多行 [英] How to delete multiple rows from different tables in single query

问题描述

在单个查询中从多个表中删除行时遇到问题.
我的表是:事件-父表，其中具有EventID作为主键.
包含EventID作为外键的其他表是:

协调员
Event_Attendee_Organizer
Event_Organizer
邀请
插槽

我想从eventid和Organizerid匹配的所有表中删除特定行. OrganizerID存在于Event_Attendee_Organizer和Event_Organizer表中.

请帮助我解决此问题.

在此先感谢...

Hi,


I am facing an issue while deleting row from multiple table in a single query.
My tables are : Event - Parent Table which have EventID as an primary key in it.
The other tables which contains the EventID as foreign key are :

Coordinator
Event_Attendee_Organizer
Event_Organizer
Invitation
Slot

I want to delete the particular rows from all the tables where eventid and organizerid matches. The OrganizerID is present in Event_Attendee_Organizer and Event_Organizer table.

Please help me to solve this issue.

Thanks in advance...

推荐答案

尝试以下操作:

Try this:

DELETE t1,t2,t3 FROM table1 as t1
JOIN table2 as t2 ON t2.ID = t1.ID
JOIN table3 as t3 ON t3.ID = t1.ID

您在所有表中的eventID 都将使其正常工作.

对于从多个表中删除记录:
您可以使用 ON DELETE CASCADE 为引用主表ID的其他表定义Foreign Key constraints(已定义为EventID 的).
这将导致这些表中的相关行被删除.这是最少的编程模型，是SQL 标准的一部分，等等.只需确保仔细考虑始终删除引用行的问题即可.
请参阅以获取更多详细信息: http://msdn.microsoft.com/en-us/library/ms174979.aspx [ ^ ]

您可以在表上创建一个delete trigger，在其中删除 ID 的主行，然后该主行将包含用于删除相关行的代码
从其他表.
您仍然必须为此编写代码，但是只要trigger 未被禁用，它就隐藏在trigger 内部，并由表中的所有删除操作使用.
请参阅以获取更多详细信息: http://msdn.microsoft.com/en-us/library/ms189799.aspx [ ^ ]

Your eventID in all table will make it work.

For deleting records from multiple tables:
You could define Foreign Key constraints (which you have defined as EventID) for the other tables that reference the master table''s ID with ON DELETE CASCADE.
This would cause the related rows in those tables to be deleted. This is the least programming model, is part of the SQL standard, etc. Just be sure that you think through the issues with always deleting the referencing rows.
Refer for more details: http://msdn.microsoft.com/en-us/library/ms174979.aspx[^]

You could create a delete trigger on the table where you delete the master row for the ID that would then contain code to delete related rows
from other tables.
You still have to write the code for this one, but it hides inside the trigger and is used by any deletes from the table as long as the trigger is not disabled.
Refer for more details: http://msdn.microsoft.com/en-us/library/ms189799.aspx[^]

尝试一下

Try this

 BEGIN TRY
   BEGIN TRANSACTION

   DELETE EAO
   FROM Event_Attendee_Organizer EAO
   INNER JOIN [Event] E ON EAO.OrganizerID = E.EventID


   DELETE EO
   FROM Event_Organizer  EO
   INNER JOIN [Event] E ON EO.OrganizerID = E.EventID

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
END CATCH

只需对此进行级联删除.

Just do a cascade delete for this one.

这篇关于如何在单个查询中从不同表中删除多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！