SQL Stored procedure for table update

Question

0.00/5 (No votes)

See more:

Hi there.

The problem I have is a simple one really, but I have no clue how to write an efficient SQL statement to carry it out.

The problem is this:

In the data base that is being updated there is 3 tables that need to be changed depending on the data in the related table.

So

Table A is parent to Table B (A.Pk is B.Fk)
Table B is parent to Table C (B.Pk is C.Fk)

When all fields in C are all closed where C.Fk = B.Pk Then
The related row in table B is closed and
When all fields in B are all closed where B.Fk = A.Pk Then
The related row in table A is closed and

The issue I have is that i do not want to iterate through each table n times using a curser, as this could cause other components to fail due to locks.

I would be most greatful if anyone can help.

Posted 24-Jun-12 6:16am

JonCox

Add a Solution

1 solution

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)

Pascal Ganaye · Accepted Answer · 2012-06-24T08:40:00

I am not that hot in SQL but here is my attempt.
The query are quite simple at the end:

SQL

CREATE TABLE A(Id int NOT NULL PRIMARY KEY)
CREATE TABLE B(Id int NOT NULL PRIMARY KEY, A int NOT NULL)
CREATE TABLE C(Id int NOT NULL PRIMARY KEY, B int NOT NULL, Closed BIT)

-- You can have foreign key references or not
--ALTER TABLE B ADD CONSTRAINT FK_B_A FOREIGN KEY(A) REFERENCES A(Id)
--ALTER TABLE C ADD CONSTRAINT FK_C_B FOREIGN KEY(B) REFERENCES B(Id)

INSERT INTO A VALUES (1),(2),(3)
INSERT INTO B VALUES (10,1), (11,1),(12,1)
INSERT INTO B VALUES (20,2), (21,2),(22,2)

INSERT INTO C VALUES (100,10,0),(101,10,0),(102,10,0)
INSERT INTO C VALUES (110,11,0),(111,11,0),(112,11,1)
INSERT INTO C VALUES (120,12,0),(121,12,1),(122,12,1)
INSERT INTO C VALUES (200,20,1),(201,20,0),(202,20,1)
INSERT INTO C VALUES (210,21,1),(211,21,1),(212,21,1)
-- nothing in 22

------------------------------------------------------
-- DELETE C RECORDS
------------------------------------------------------
-- this is required if you have the Foreign keys
DELETE FROM C WHERE C.Closed = 1

------------------------------------------------------
-- DELETE B RECORDS
------------------------------------------------------
DELETE FROM B
    WHERE NOT EXISTS(SELECT * FROM C WHERE C.B = B.ID)

-- or if you have no foreign keys and want to keep the C records:
DELETE FROM B
    WHERE NOT EXISTS(SELECT * FROM C WHERE C.B = B.ID AND C.Closed = 0)

------------------------------------------------------
-- DELETE A RECORDS
------------------------------------------------------
DELETE FROM A
    WHERE NOT EXISTS(SELECT * FROM B WHERE B.A = A.ID)

------------------------------------------------------
-- Results
------------------------------------------------------

SELECT * FROM A

--     Id
--     -----------
--     1
--     2

-- A(3) is gone


SELECT * FROM B

-- B(21) is gone
-- B(22) is gone

--   Id          A
--   ----------- -----------
--   10          1
--   11          1
--   12          1
--   20          2

SELECT * FROM C

--     Id          B           Closed
---- ----------- ----------- ------
--   100         10          0
--   101         10          0
--   102         10          0
--   110         11          0
--   111         11          0
--   120         12          0
--   201         20          0

-- All the closed are gone

DROP TABLE C
DROP TABLE B
DROP TABLE A