SQL: How to append IDs to the rows with duplicate values

Question

I have a table with some duplicate rows. I want to modify only the duplicate rows as follows.

Before:

id     col1
------------
1      vvvv
2      vvvv
3      vvvv

After:

id     col1
------------
1      vvvv
2      vvvv-2
3      vvvv-3

Col1 is appended with a hyphen and the value of id column.

Answer 1

You might be able to do that with a sproc and cursors. I don't think it's possible in any reasonable select query.

Answer 2

You can accomplish this with a 2 step process, although an SQL wizard could probably modify this to give you a solution in one step.

First you need to get all the duplicate values. Here is an SQL query that will do that:

SELECT     COUNT(*) AS NumberOfDuplicates, col1
FROM         Table1
GROUP BY col1
HAVING      (COUNT(*) > 1)

This will give you a resultset listing the number of duplicates and the duplicate value.

In step 2 you would loop through this resultset, fetch the col1 value, return all the records containing that value and (possibly using a loop counter variable) alter the value as per your example.

Note: you don't really need to return the number of duplicates to achieve your goal, but it will help you to test the query and be satisfied that it works.

Answer 3

Check IN out in Oracle Syntax. The query is not tested

update table1 set
col1 = col1 || id
where
id not in (
select min(id) from table1
groupby col1
)

Answer 4

This SQL will only update duplicates, but not the one with the lowest id :

update tbl
  set col1 = col1 + '-' + convert(varchar, id)
  where exists(select * from tbl t where t.col1 = tbl.col1 and t.id < tbl.id)