Currently my table looks like this; I want to add the count numbers with distinct InstanceId and duplicate values.

I want the output to be like this:

I don't want to update the first value and update should start with the next duplicate value in the column.

How to update this table to make this output possible in SQL Server query?

CodePudding user response：

Another option is using the row_number() like this
This solution uses your new column instanceid to get the correct data

select t.id,
       case when rownumber > 1 then t.Name   '_Backup_'   convert(varchar(10), t.rownumber - 1)
            else t.Name
       end     
from   ( select t.id,
                t.name,
                row_number() over (partition by t.Name, t.instanceid order by t.id) as rownumber
         from   mytable t
       ) t
order by t.id

See this DBFiddle

output is

CodePudding user response：

EDIT This solution addresses the ORIGINAL question and original output. This is no longer valid because you changed your desired output.

You could use rank() and concat in this manner:

with cte as (select id, name, rank() over (partition by name order by id) as name_rank
from my_table
)
select t.id, 
case
 when c.name_rank = 1 then t.name
 else concat(t.name, '_Backup_', c.name_rank - 1)
end name
from my_table t
join cte c
  on t.id = c.id

Output:

id	name
1	DiscoveryInstance
2	DiscoveryInstance_Backup_1
3	ETLInstance
4	DiscoveryInstance_Backup_2
5	DiscoveryInstance_Backup_3
6	ETLInstance_Backup_1

DB-fiddle found here. I see you updated the question after I posted this answer by adding another column, but that does not look important at the moment.

EDIT

This is an updated answer (thanks Guido) that would address your newly updated output:

with cte as (select id, name, rank() over (partition by name, instanceid order by id) as name_rank
from mytable
)
select t.id, 
case
 when c.name_rank = 1 then t.name
 else concat(t.name, '_Backup_', c.name_rank - 1)
end name
from mytable t
join cte c
  on t.id = c.id