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

Question

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.

Can you show an extended sample data set? Is there any order at all to the `ID` numbering scheme and the column name? — Tim Biegeleisen, Dec 07 '15 at 05:44
No I created a temporary table with ID as(Integer identity)... the column names have the ID values as per the insertion. All I need is the replacement for similar string names with some altenate name or number added to it.... thanks for your comment :) — Gowtham Ramamoorthy, Dec 07 '15 at 05:47

score 3 · Accepted Answer · answered Dec 07 '15 at 05:54

3

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

answered Dec 07 '15 at 05:54

Nguyễn Hải Triều

1,454
1
8
14

You're welcome. P/s: `CAST(RN AS varchar(1))` just support 1-->9, if you want more, you can extend `varchar(n) -- n=2 (1-->99), n=3 (1-->999),...` – Nguyễn Hải Triều Dec 07 '15 at 06:05
Hi... when i use the querey all the string names are getting appended with numeric value1.. – Gowtham Ramamoorthy Dec 07 '15 at 06:16

score 2 · Answer 2 · answered Dec 07 '15 at 05:54

2

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

answered Dec 07 '15 at 05:54

Tim Biegeleisen

502,043
27
286
360

1

It works, but just select, i think he would update his table :) – Nguyễn Hải Triều Dec 07 '15 at 06:05
Thanks @NguyễnHảiTriều your answer is way more concise. I will leave this up in case someone just wants to select. – Tim Biegeleisen Dec 07 '15 at 06:06
i fear [CONCAT](http://stackoverflow.com/questions/10550307/how-do-i-use-the-concat-function-in-sql-server-2008-r2) will be available from 2012. – Ragul Dec 07 '15 at 07:27
@Ragul The link you provided seems to say that `CONCAT` is available in SQL Server 2008 and beyond. This is a relatively old feature. – Tim Biegeleisen Dec 07 '15 at 07:29

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

2 Answers2