Question

I have a table like below

DECLARE @ProductTotals TABLE 
(
  id int, 

  value nvarchar(50)
)

which has following value

1,  abc 
2,  abc 
1,  abc 
3,  abc

I want to update this table so that it has the following values

1,  abc 
2,  abc_1 
1,  abc 
3,  abc_2

Could someone help me out with this

Answer 1

Use a cursor to move over the table and try to insert every row in a second temporary table. If you get a collision (technically with a select), you can run a second query to get the maximum number (if any) that s appended to your item.

一旦你知道使用最大数目(使用<代码>snull,以涵盖第一个重复病例),就在你原来的表格上进行了更新,并与你的扫描保持同步。

Answer 2

Are you looking to remove duplicates? or just change the values so they aren t duplicate?

to change the values use

update producttotals set value = abc_1 where id =2;

update producttotals set value = abc_2 where id =3;

to find duplicate rows do a select id, value from producttotals group by id, value having count() > 2;

Answer 3

Assuming SQL Server 2005 or greater

DECLARE @ProductTotals TABLE 
(
  id int, 

  value nvarchar(50)
)
INSERT INTO @ProductTotals
VALUES (1,  abc ),
        (2,  abc ),
        (1,  abc ),
        (3,  abc )


;WITH CTE as 
(SELECT 
    ROW_NUMBER() OVER (Partition by value order by id) rn,
    id,
    value
FROM
    @ProductTotals),
new_values  as (
SELECT
    pt.id,
    pt.value,
    pt.value +  _  +  CAST( ROW_NUMBER() OVER (partition by pt.value order by pt.id) as varchar) new_value



FROM
    @ProductTotals pt
    INNER JOIN CTE
    ON pt.id = CTE.id
     and pt.value = CTE.value   
WHERE
    pt.id NOT IN (SELECT id FROM CTE WHERE rn = 1)) --remove any with the lowest ID for the value

UPDATE
    @ProductTotals 
SET
    pt.value = nv.new_value
FROM 
@ProductTotals pt 
inner join new_values nv
ON pt.id = nv.id and pt.value = nv.value

SELECT * FROM @ProductTotals

Will produce the following

id          value
----------- --------------------------------------------------
1           abc
2           abc_1
1           abc
3           abc_2

Explanation of the SQL

The first CTE creates a row number Value. So the numbering gets restarted whenever it sees a new value

rn                   id          value
-------------------- ----------- --------
1                    1           abc
2                    1           abc
3                    2           abc
4                    3           abc

The second CTE called new_values ignores any IDs that are assoicated with with a RN of 1. So rn 1 and rn 2 get removed because they share the same ID. It also uses ROW_NUMBER() again to determine the number for the new_value

id          value  new_value
----------- ------ -------------
2           abc    abc_1
3           abc    abc_2

The final statement just updates the Old value with the new value

友情链接