CODEWITHSUNDEEP
sqlsql-server
Sanjeev S
Sanjeev S

Reputation: 626

How to update value if one of the field has multiple same values in SQL

In my database table, one of the fields has the same values. I want to change this value with random number or string which is appended to this field value for uniqueness.

Sample Data Here 'Ma' has 5 records and so on. I want to change Name Ma01, Ma02 etc.

Id  Name  Count
1   Ma   5
2   Ga   6
3   Gu   5

How can do with SQL query

Upvotes: 1

Views: 77

Answers (4)

WernerW
WernerW

Reputation: 812

Select Code based on NEER`s code with leading zero added, tested on MSSQL database.

SELECT
    Id,
    Name,
    Name + RIGHT('00'+CAST(ROW_NUMBER() OVER (PARTITION BY Name ORDER BY (SELECT Name)) AS NVARCHAR(MAX)),2) AS Updatedname

FROM
    tablename
WHERE
    Name IN 
    (
        SELECT T.NameFROM tablename T
        GROUP BY T.Name
        HAVING COUNT(Name) > 1
    )
GROUP BY Name,Id
ORDER BY Name

Upvotes: 0

Ajay2707
Ajay2707

Reputation: 5798

You just try this.

Declare @i int  = 1

update yourtable
set ID  = @i , @i = @i + 1

Upvotes: 0

neer
neer

Reputation: 4082

Try this

UPDATE TBL
SET Name = A.Name
FROM
(
    SELECT
        Id,
        Name + CAST(ROW_NUMBER() OVER (PARTITION BY Name ORDER BY (SELECT NULL)) AS NVARCHAR(500)) AS NAME,
        Count
    FROM
        TBL
    WHERE
        NAME IN 
        (
            SELECT T.NAME FROM TBL T
            GROUP BY T.NAME
            HAVING COUNT(1) > 1
        )

) A
WHERE
    TBL.Id = A.ID

Upvotes: 2

Vorpulus Lyphane
Vorpulus Lyphane

Reputation: 702

Try this:

Select Name + convert(varchar(2), row_number()over(partition by Name order by Name))
From tablename

Upvotes: 0

Related Questions