sql - 如果返回超过 1 个 MS SQL，则删除

Question

如果行数超过 1，我需要删除行。为什么它不起作用？

if
    (SELECT count(*)
FROM Table1 where Name = 'TEST')

RETURN (2)

BEGIN
    DELETE MIN(Id) FROM Table1 where Name = 'TEST'
END

Answer 1

你的语法完全错误。您不能从表达式中删除；您从表中删除。

您可以使用窗口函数和可更新的 CTE 来执行您想要的操作。如果您想删除一行的所有个副本，请使用count(*):

with todelete as (
      select t1.*,
             count(*) over (partition by t1.name) as cnt
      from table1 t1
      where Name = 'TEST'
     )
delete from todelete
    where cnt > 1;

我猜测您实际上想要保留具有最小id的行。在这种情况下，请使用 row_number():

with todelete as (
      select t1.*,
             row_number() over (partition by t1.name order by t1.id) as seqnum
      from table1 t1
      where Name = 'TEST'
     )
delete from todelete
    where seqnum > 1;