用于表更新的SQL存储过程 [英] SQL Stored procedure for table update
问题描述
你好.
我的问题确实是一个简单的问题,但是我不知道如何编写有效的SQL语句来执行它.
问题是这样的:
在要更新的数据库中,根据相关表中的数据,需要更改3个表.
所以
表A是表B的父级(A.Pk是B.Fk)
表B是表C的父级(B.Pk是C.Fk)
当C中的所有字段都关闭时,C.Fk = B.Pk然后
表B中的相关行已关闭,
当B中的所有字段都关闭时,其中B.Fk = A.Pk然后
表A中的相关行已关闭,并且
我遇到的问题是我不想使用curser遍历每个表n次,因为这可能导致其他组件由于锁定而失败.
如果有人可以提供帮助,我将非常感激.
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.
推荐答案
我在SQL中并不那么热门,但这是我的尝试.
最后的查询非常简单:
I am not that hot in SQL but here is my attempt.
The query are quite simple at the end:
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
这篇关于用于表更新的SQL存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!