CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2sql-update
vborutenko
vborutenko

Reputation: 4443

Update duplicate fields in table

I have table with about 100000 records.I need update same fields like this. For example this is my table

id     name
 1     sss
 2     bbb
 3     ccc
 4     avg
 5     bbb
 6     bbb
 7     sss
 8     mmm
 9     avg

After executing script I need get

id     name
 1     sss
 2     bbb
 3     ccc
 4     avg
 5     bbb-5
 6     bbb-6
 7     sss-7
 8     mmm
 9     avg-9

How can I do that?

Upvotes: 2

Views: 78

Answers (2)

John Woo
John Woo

Reputation: 263693

By using CTE

WITH greaterRecord 
AS 
(
    SELECT  id, 
            name, 
            ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) RN
    FROM    TableName
)
UPDATE greaterRecord
SET    name = name + '-' + CAST(id AS VARCHAR(10))
WHERE  RN > 1

This is the common query that works on most RDBMS

UPDATE  a
SET     a.Name = a.Name + '-' + CAST(ID AS VARCHAR(10))
FROM    tableName a
        LEFT JOIN
        (
            SELECT  MIN(ID) min_ID, name
            FROM    tableName
            GROUP   BY name
        ) b ON  a.name = b.name AND
                a.ID = b.Min_ID
WHERE   b.Name IS NULL

OUTPUT after the update statement has been executed

╔════╦═══════╗
║ ID ║ NAME  ║
╠════╬═══════╣
║  1 ║ sss   ║
║  2 ║ bbb   ║
║  3 ║ ccc   ║
║  4 ║ avg   ║
║  5 ║ bbb-5 ║
║  6 ║ bbb-6 ║
║  7 ║ sss-7 ║
║  8 ║ mmm   ║
║  9 ║ avg-9 ║
╚════╩═══════╝

Upvotes: 3

Lamak
Lamak

Reputation: 70638

This should do:

;WITH CTE AS 
(
    SELECT  id, 
            name, 
            RN = ROW_NUMBER() OVER(PARTITION BY name ORDER BY id)
    FROM YourTable
)
UPDATE CTE
SET name = name + '-' + CAST(id AS VARCHAR(8))
WHERE RN > 1

Upvotes: 3

Related Questions