CODEWITHSUNDEEP
sqlsql-server-2008
Gowtham Ramamoorthy
Gowtham Ramamoorthy

Reputation: 896

How to replace values which are equal in a column in SQL?

I have the below column in a table which has same string values. I need to replace the strings which are equal for eg:

+----+-------------+
| ID | Column_Name |
+----+-------------+
|  1 | Imports     |
|  2 | Imports     |
|  3 | Exports     |
|  4 | Exports     |
+----+-------------+

Which needs to be replaced as :

+----+-------------+
| ID | Column_Name |
+----+-------------+
|  1 | Imports1    |
|  2 | Imports2    |
|  3 | Exports1    |
|  4 | Exports2    |
+----+-------------+

Is there a way to achieve this in SQL query ?

The purpose of doing this is that I'm using the 'Column_Name' string values in the dynamic SQL to replace the Integer values because of the same names the different values for the column are not getting replaced.

Upvotes: 2

Views: 1691

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Here is a single which achieves what you want. It performs a union of two inner queries, each of which numbers the Import and Export records from 1, in the order of their original ID values. The outer query selects the ID along with the new Column Name.

SELECT ID, CONCAT([Column Name], NewID)
FROM
(
    SELECT ID, [Column Name],
    ROW_NUMBER() OVER (ORDER BY ID ASC) AS NewID
    FROM your_table
    WHERE [Column Name] = 'Exports'
    UNION ALL
    SELECT ID, [Column Name],
        ROW_NUMBER() OVER (ORDER BY ID ASC) AS NewID
    FROM your_table
    WHERE [Column Name] = 'Imports'
)
ORDER BY ID ASC

Upvotes: 2

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

You can try this:

UPDATE Your_Table
SET Name = Name + CAST(RN AS varchar(1)) FROM -- varchar(10)
                    (SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS RN
                     FROM Your_Table) AS A

Upvotes: 3

Related Questions