如何将我的SELECT查询转换为UPDATE以追加到SQL Server中的字段？

I have a select statement that selects all the duplicates in my table based on a certain criteria. I need to UPDATE all of these records with a field called BAD_CODES

我有一个select语句，根据特定条件选择表中的所有重复项。我需要使用名为BAD_CODES的字段更新所有这些记录

So for example, if we have two duplicate fields called Tom, then we want to UPDATE the BAD_CODES field to append the letter 'D'.

因此，例如，如果我们有两个名为Tom的重复字段，那么我们想要更新BAD_CODES字段以附加字母'D'。

Here is my select statement:

这是我的选择声明：

SELECT
    division, fips_county_code, last, suffix, first, title, birthdate, COUNT(*)
FROM
    WORK
GROUP BY
    division, fips_county_code, last, suffix, first, title, birthdate
HAVING 
    COUNT(*) > 1

This is what I have tried as far as my UPDATE statement:

就我的UPDATE语句而言，这就是我所尝试的：

UPDATE WORK
 SET BAD_CODES = ISNULL(BAD_CODES, '') + 'D'
 WHERE (SELECT
    division, fips_county_code, last, suffix, first, title, birthdate, COUNT(*)
FROM
    WORK
GROUP BY
    division, fips_county_code, last, suffix, first, title, birthdate
HAVING 
    COUNT(*) > 1)

3 个解决方案

#1

You can do this using CTE. Use Count() Over() Window aggregate function to count the records for each group and update the records when count is greater than 1

您可以使用CTE执行此操作。使用Count（）Over（）窗口聚合函数来计算每个组的记录，并在count大于1时更新记录

WITH cte
     AS (SELECT *,
                Count(*) OVER(partition BY division, fips_county_code, last, suffix, first, title, birthdate) AS cnt
         FROM   WORK)
UPDATE cte
SET    BAD_CODES = Isnull(BAD_CODES, '') + 'D'
WHERE  cnt > 1

If you want to leave one record from duplicate then use ROW_NUMBER

如果要从重复中保留一条记录，请使用ROW_NUMBER

WITH cte
     AS (SELECT *,
                Row_Number() OVER(partition BY division, fips_county_code, last, suffix, first, title, birthdate Order by (select null)) AS Rn
         FROM   WORK)
UPDATE cte
SET    BAD_CODES = Isnull(BAD_CODES, '') + 'D'
WHERE  RN > 1

In Order by (select null) replace (Select Null) with the column in which you to order the record and filter it from updating

在Order by（select null）中，用您在其中订购记录的列替换（选择Null）并从更新中过滤它

#2

update w
set bad_codes='D'
from
work w
join
(SELECT
    division, fips_county_code, last, suffix, first, title, birthdate, COUNT(*)
FROM
    WORK
GROUP BY
    division, fips_county_code, last, suffix, first, title, birthdate
HAVING 
    COUNT(*) > 1
) dup
on 
dup.joinclause=w.joinclause
....

#3

You can update using join:

您可以使用join进行更新：

update w
set w.bad_codes = isnull(bad_codes, '') + 'D' from
work w inner join (
    SELECT
    division, fips_county_code, last, suffix, first, title, birthdate, COUNT(*)
    FROM
        WORK
    GROUP BY
        division, fips_county_code, last, suffix, first, title, birthdate
    HAVING 
        COUNT(*) > 1
) w2 on w.division = w2.division
    and w.fips_county_code = w2.fips_county_code
    and w.last = w2.last
    and w.first = w2.first
    and w.birthdate = w2.birthdate

#1