时间戳

首页 > 解决方案 > SQL 删除带有子查询且不存在的行

sql-server - SQL 删除带有子查询且不存在的行

问题描述

我有一个查询以返回具有特定状态的重复行。查询返回正确，但我还需要从 Table1 中删除行。当我将 SELECT 更改为 DELETE 语句时，它会删除所有记录，而不仅仅是基于我的查询的子集。我究竟做错了什么？这是返回预期行的查询：

SELECT *
FROM
(
    SELECT *, ROW_NUMBER() over (PARTITION BY ID_1 ORDER BY Status) RowNumber
    FROM Table1
) X
WHERE (((X.RowNumber > 1) and (X.Status = 1)) AND NOT EXISTS (SELECT * FROM Table2 WHERE X.ID_1 = Table2.ID_2))

现在我只想删除上面查询的结果，但是下面会删除 Table1 中的所有记录：

DELETE Table1
FROM
(
    SELECT *, ROW_NUMBER() over (PARTITION BY ID_1 ORDER BY Status) RowNumber
    FROM Table1
) X
WHERE (((X.RowNumber > 1) and (X.Status = 1)) AND NOT EXISTS (SELECT * FROM Table2 WHERE X.ID_1 = Table2.ID_2))

标签： sql-servertsql

解决方案

正如Squrirrel评论中提到的，我已经用一些虚拟数据测试了你的代码，我发现它有效。更改DELETE Table1为DELETE X. 这是我的示例代码，对我有用。

DECLARE @Table1 TABLE(ID_1 INT,INFO VARCHAR(20),[Status] SMALLINT);
DECLARE @Table2 TABLE(ID_2 INT,INFO VARCHAR(20),[Status] SMALLINT);
INSERT INTO @Table1 VALUES(1,'a',1);
INSERT INTO @Table1 VALUES(1,'b',1);
INSERT INTO @Table1 VALUES(1,'a',2);
INSERT INTO @Table1 VALUES(1,'b',2);
INSERT INTO @Table1 VALUES(2,'a',1);
INSERT INTO @Table1 VALUES(2,'b',1);
INSERT INTO @Table2 VALUES(2,'b',1);

DELETE X
FROM
(
    SELECT *, ROW_NUMBER() over (PARTITION BY ID_1 ORDER BY Status) RowNumber
    FROM @Table1
) X
WHERE (((X.RowNumber > 1) and (X.Status = 1)) AND NOT EXISTS (SELECT * FROM @Table2 t2 WHERE X.ID_1 = t2.ID_2));

推荐阅读