CODEWITHSUNDEEP
sqlsql-serverstored-procedures
kriss
kriss

Reputation: 1015

Procedure deleting related rows

I want to implement procedure that will delete rows from 3 related table.

I have table, A, B, and C.

Table A
ID

Table B
ID
tableAId

Table C
ID
tablceBId

relation table A - B is one to many and relation B - C is one to many.

Now I want to implement a procedure that would delete all rows that are related with Table A. I do not want to make these delete on the cascade. Can I achieve this in one query?

I pass ID of row in Table A.

Upvotes: 0

Views: 52

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

CREATE PROCEDURE delTableA
@tableA_ID int
AS
BEGIN
    DELETE C
    FROM C
    INNER JOIN B ON C.ID = B.tableCId
    INNER JOIN A ON B.ID = A.tableBId
    WHERE A.ID = @tableA_ID

    DELETE B
    FROM B
    INNER JOIN A ON B.ID = A.tableBId 
    WHERE A.ID = @tableA_ID

    DELETE FROM A
    WHERE A.ID = @tableA_ID
END
GO

As in,

CREATE TABLE C (ID int)
CREATE TABLE B (ID int, tableCId int)
CREATE TABLE A (ID int, tableBId int)
GO
INSERT INTO c VALUES (1)
INSERT INTO b VALUES (1, 1)
INSERT INTO a VALUES (1, 1) 
GO

CREATE PROCEDURE delTableA
@tableA_ID int
AS
BEGIN
    DELETE C
    FROM C
    INNER JOIN B ON C.ID = B.tableCId
    INNER JOIN A ON B.ID = A.tableBId
    WHERE A.ID = @tableA_ID

    DELETE B
    FROM B
    INNER JOIN A ON B.ID = A.tableBId 
    WHERE A.ID = @tableA_ID

    DELETE FROM A
    WHERE A.ID = @tableA_ID
END
GO
EXEC delTableA 1
GO

If you run this query, all are empty:

select * from A
select * from B
select * from C

Upvotes: 1

Related Questions