Sanjeev S Sanjeev S - 8 months ago 20
SQL Question

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

Answer

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