Sanjeev S Sanjeev S - 4 months ago 6
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
Comments