如何在SQL Server 2005中更新非重复列？ [英] How to update distinct column in SQL Server 2005?

问题描述

在我的表格中，我有一列 Segment_No ，其中有重复项。现在，我要更新这些重复项。

In my table I have a column Segment_No which has duplicates. Now I want to update those duplicates.

例如： 249X5601 出现在两行中。我想将第二个值更改为 249X5601R

For example: the value 249X5601 is present in two rows. I want to change the second value to 249X5601R

推荐答案

一般形式如下：

;with AllRows as (
    select Donation_ID,Registration_No,Segment_No,
           ROW_NUMBER() OVER (
               PARTITION BY Segment_No
               order by <Suitable_Column>
           ) as rn
    from UnnamedTable
)
update AllRows set Segment_no = <New_Value>
where rn > 1

其中< Suitable_Column> 给出列定义哪一行是第一，哪一行是第二。 < New_Value> 定义了应如何计算新的 Segment_no 值和 rn 给出行号-因此where子句忽略了第一行。

Where <Suitable_Column> gives the column(s) the define which row is "first" and which is second. <New_Value> defines how the new Segment_no value should be computed, and rn gives the row numbers - so the where clause is ignoring the "first" row.

因此，如果最多只有两行共享一个 Segment_no 值，而第一个是具有最低 Donation_ID 值的那个，那么它将是： / p>

So if there are only ever a max of two rows sharing a single Segment_no value, and the "first" is the one with the lowest Donation_ID value, then it would be:

;with AllRows as (
    select Donation_ID,Registration_No,Segment_No,
           ROW_NUMBER() OVER (
               PARTITION BY Segment_No
               order by Donation_ID
           ) as rn
    from UnnamedTable
)
update AllRows set Segment_no = Segment_no + 'R'
where rn > 1

这篇关于如何在SQL Server 2005中更新非重复列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！