how to delete data from three tables

Question

0.00/5 (No votes)

See more:

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

pls give me suggestions

Thanks & regards
venkatesh

Posted 20-Apr-10 0:39am

venkatesh.gan

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhishek Banerjee (A Web Developer) · Answer 1 · 2010-04-20T00:51:00

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

Dylan Morley · Answer 2 · 2010-04-20T00:51:00

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

SQL

--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

WoutL · Answer 3 · 2010-04-20T00:49:00

Solution 1

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.

Posted 20-Apr-10 0:49am

WoutL

how to delete data from three tables

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0