如何从三个表中删除数据 [英] how to delete data from three tables

问题描述

我有三个与聚会相关的表.如何删除这些记录.

请给我建议

谢谢与问候
venkatesh

I have three tables which are related togather.How should I delete those records.

pls give me suggestions

Thanks & regards
venkatesh

推荐答案

您好，Venkatesh，

如果您有三个表，则只需分析它们之间的主键和外键关系，即使与其他表也是如此，如果您获得的PK和FK关系仅存在于这三个表中，则从FK到PK的相反方向删除记录例如，如果您在表1中输入数据，并且主键(PK)是表2的外键(FK)，然后表2的PK是表3的FK，则删除数据，如下所示

< b>表3 --->表2 ------>表1</b>


谢谢&问候:-D
abhi.22web.net

Hi Venkatesh,

If you have three tables then just analysis there primary and foreign key relationship with each other, even with other tables also if you get that P.K and F.K relation is exist only within these three tables then delete the record in reverse direction according FK to PK from the tables.Like If you enter data in Table1 and Primary Key(P.K) is Foreign Key(F.K) of Table 2 and Then P.K of Table 2 is F.K of Table 3 the delete data as given below

<b>Table 3---> Table 2------> Table 1 </b>


Thanks & Regards :-D
abhi.22web.net

从最低级别删除，受事务控制

在此示例中，订单"表与订单目录详细信息"相关，而订单目录详细信息"与订单数量"相关.

如果您使用的是SQL 2005，那么显然可以尝试捕获而不是这种类型的错误检查

Delete from the lowest level up, transaction controlled

In this example, the ''Orders'' table is related to ''Order Catalogue Details'' which is related to ''Order Quantities''.

If you''re using SQL 2005 then obviously try-catch rather than this type of error checking

--We're deleting from multiple tables, so trans control
BEGIN TRANSACTION

--Lowest level first, attempt to delete the order quantities
    DELETE FROM order_quantities WHERE oq_catalogue_id_fk IN (SELECT ocd_id_pk FROM order_catalogue_details WHERE ocd_order_id_fk = @OrderID)
    IF  (@@Error !=0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @@ERROR
    END

--Now delete all the catalogue items for the order
    DELETE FROM order_catalogue_details WHERE ocd_order_id_fk = @OrderID
    IF  (@@Error !=0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @@ERROR
    END

--remove the main order information
    DELETE FROM orders WHERE ord_id_pk = @OrderID
    IF  (@@Error !=0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @@ERROR
    END

COMMIT TRANSACTION

如果定义了关系在带有级联删除相关记录"的表之间，单个删除就足够了.否则，您可以使用存储过程来执行这三个delete语句.

If you define the relations between the tables with ''Cascade Delete Related records'' a single delete could be enough. Otherwise you could use a stored procedure to perform the three delete statements.

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