代码之家 › 专栏 › 技术社区 › Jamie Rees

在一个查询中使用左联接从多个表中删除

tsql sql-server sql

Jamie Rees · 技术社区 · 7 年前

我想知道我是否可以做到以下几点,我有3个表,我想删除的数据:表1,表2,表3

世界上总会有一张唱片 Table1 可能有也可能没有关联的记录 Table2 和 Table3 .

所以我想我可以做到以下几点:

DELETE FROM dbo.Table1, dbo.Table2, dbo.Table3
FROM Table1 t1
LEFT JOIN dbo.Table2 t2 ON t1.Id = t2.Id
LEFT JOIN dbo.Table3 t3 ON t3.Id = t1.Id
WHERE t1.Id = @Id;

但我得到一个 Incorrect syntax near ','. DELETE FROM dbo.Table1, dbo.Table2, dbo.Table3 线路。

期望的结果是使用 Where 条款。

我不知道该怎么做。

4 回复 | 直到 7 年前

Gordon Linoff 7 年前

一次只能从一个表中删除。想必,您需要级联约束:

alter table2 add constraint fk_table2_table1
    foreign key (id) references table1(id)
    on delete cascade;

alter tablee add constraint fk_tablee_table1
    foreign key (id) references table1(id)
    on delete cascade;

外键确保数据完整性。级联约束意味着当主键被删除时,其他表中相应的行也被删除。

Thom A 7 年前

无论如何,触发方法是:

CREATE TRIGGER Cascade_Table2_Table3 ON dbo.Table1
AFTER DELETE
AS

    DELETE T2
    FROM Table2 T2
         JOIN deleted d ON t2.Id = d.Id;

    DELETE T3
    FROM Table3 T3
         JOIN deleted d ON t3.Id = d.Id;
GO

但是,由于您使用的是单个值,所以参数化存储过程会更好;然后你呢 DENY 运行 DELETE 在桌子上:

CREATE PROC Delete_T1T2T3 @ID int AS

    DELETE FROM dbo.Table1
    WHERE ID = @ID;

    DELETE FROM dbo.Table2
    WHERE ID = @ID;

    DELETE FROM dbo.Table3
    WHERE ID = @ID;

GO

user10679624 user10679624 7 年前

你能在表1上使用触发器吗?所以你只能从表1中删除记录,然后让触发器从表2和表3中删除内容。

比如:

CREATE TRIGGER [dbo].[T_deleteT2_t3]
    ON [dbo].[Table1]
    AFTER  DELETE
AS

BEGIN

    delete from table2 where id = select id from deleted
    delete from table3 where id = select id from deleted

END

Ilyes 7 年前

OUTPUT 条款

CREATE TABLE T1( ID INT);
CREATE TABLE T2( ID INT);
CREATE TABLE T3( ID INT);

INSERT INTO T1 VALUES (1), (2);
INSERT INTO T2 VALUES (1), (3);
INSERT INTO T3 VALUES (1), (4);

BEGIN TRAN;

 DECLARE @Out TABLE (IDs INT);

 DELETE T1
 OUTPUT DELETED.ID INTO @Out
 FROM T1 INNER JOIN T2 ON T1.ID = T2.ID;

 DELETE T2
 FROM T2 INNER JOIN @Out O ON T2.ID = O.IDs;

 DELETE T3
 FROM T3 INNER JOIN @Out O ON T3.ID = O.IDs;

COMMIT TRAN;

SELECT *
FROM T1;
SELECT *
FROM T2;
SELECT *
FROM T3;

Demo

Redkram 5 年前

**DELETE t1,t2,t3 FROM**
FROM Table1 t1
LEFT JOIN dbo.Table2 t2 ON t1.Id = t2.Id
LEFT JOIN dbo.Table3 t3 ON t3.Id = t1.Id
WHERE t1.Id = @Id;