用于表更新的SQL存储过程 [英] SQL Stored procedure for table update

查看:76
本文介绍了用于表更新的SQL存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

你好.

我的问题确实是一个简单的问题,但是我不知道如何编写有效的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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆