创建从多个表中删除数据的过程.数据库服务器 [英] Create procedure that deletes data from several tables. SQL Server

问题描述

我需要创建一个过程，允许我一次从多个表中删除数据，搜索CUIT.这里显示了四个表:

I need to create a procedure that allows me to delete data from several tables at once, searching through the CUIT. There are four tables shown here:

我必须传入一个 CUIT 值，如果它与数据库中保存的任何项目匹配，我需要将其删除.我找不到能够一次擦除两个以上表的语法，如果你能帮我一把，我将不胜感激.我怀疑该过程实际上要大得多，总共大约有 12 个表，但由于我只对语法感兴趣，所以我传递了这个片段.

I must pass a CUIT value in, and if it matches any item saved in the database, I need it to be deleted. I could not find the syntax to be able to erase more than two tables at once, if you could give me a hand I would greatly appreciate it. I clarify by the doubts that the procedure is actually much larger, in total it would be about 12 tables, but as I am interested only the syntax I pass this fragment.

我在尝试使用 INNER JOIN 连接表时遇到问题.

I have problems trying to join the tables using INNER JOIN.

我使用的是 SQL Server 2016.谢谢

I am using SQL Server 2016. Thank you

推荐答案

考虑到您的设计，我会在您的外键上实现级联.这是一个示例，但应该能让您走上正确的道路:

Considering your design, i would implement Cascading on your Foreign Key. This is a sample, but should get you on the right path:

USE Sandbox;
GO

CREATE TABLE dbo.ParentTable (PK int IDENTITY PRIMARY KEY,
                              I int);

CREATE TABLE dbo.ChildTable1 (PK int IDENTITY PRIMARY KEY,
                              FK int,
                              S varchar(10));

CREATE TABLE dbo.ChildTable2 (PK int IDENTITY PRIMARY KEY,
                              FK int,
                              S varchar(10));

CREATE TABLE dbo.ChildTable3 (PK int IDENTITY PRIMARY KEY,
                              FK int,
                              S varchar(10));
GO
--Create keys with CASCADE ON DELETE
ALTER TABLE dbo.ChildTable1 ADD CONSTRAINT FK1 FOREIGN KEY (FK) REFERENCES dbo.ParentTable(PK) ON DELETE CASCADE;
GO
ALTER TABLE dbo.ChildTable2 ADD CONSTRAINT FK2 FOREIGN KEY (FK) REFERENCES dbo.ParentTable(PK) ON DELETE CASCADE;
GO
ALTER TABLE dbo.ChildTable3 ADD CONSTRAINT FK3 FOREIGN KEY (FK) REFERENCES dbo.ParentTable(PK) ON DELETE CASCADE;
GO


INSERT INTO dbo.ParentTable (I)
VALUES(1),(7),(9);

INSERT INTO dbo.ChildTable1 (FK,
                            S)
VALUES(1,'abc'),(3,'def');

INSERT INTO dbo.ChildTable2 (FK,
                            S)
VALUES(1,'xyz'),(2,'qwe');

INSERT INTO dbo.ChildTable3 (FK,
                            S)
VALUES(1,'123');
GO

SELECT *
FROM dbo.ParentTable PT
     LEFT JOIN dbo.ChildTable1 CT1 ON PT.PK = CT1.PK
     LEFT JOIN dbo.ChildTable2 CT2 ON PT.PK = CT2.PK
     LEFT JOIN dbo.ChildTable3 CT3 ON PT.PK = CT3.PK;

--1, as we're going to DELETE that
SELECT *
FROM dbo.ChildTable1 CT1
WHERE CT1.FK = 1;

SELECT *
FROM dbo.ChildTable1 CT2
WHERE CT2.FK = 1;

SELECT *
FROM dbo.ChildTable1 CT3
WHERE CT3.FK = 1;
GO

--Perform the DELETE, which will CASCADE to the child tables.    
DELETE FROM dbo.ParentTable
WHERE PK = 1;
GO
--no rows
SELECT *
FROM dbo.ParentTable PT
     LEFT JOIN dbo.ChildTable1 CT1 ON PT.PK = CT1.PK
     LEFT JOIN dbo.ChildTable2 CT2 ON PT.PK = CT2.PK
     LEFT JOIN dbo.ChildTable3 CT3 ON PT.PK = CT3.PK;

SELECT *
FROM dbo.ChildTable1 CT1
WHERE CT1.FK = 1;

SELECT *
FROM dbo.ChildTable1 CT2
WHERE CT2.FK = 1;

SELECT *
FROM dbo.ChildTable1 CT3
WHERE CT3.FK = 1;

GO
DROP TABLE dbo.ChildTable3;
DROP TABLE dbo.ChildTable2;
DROP TABLE dbo.ChildTable1;
DROP TABLE dbo.ParentTable;

这篇关于创建从多个表中删除数据的过程.数据库服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！