How to add +1 one to a Name in case of repeated registration?

Question

How to add +1 one to a Name in case of repeated registration?

Navigation

#1 by (1 votes)

1

I have a column called NOMBRE . In this there are three patients called "Juan". What I want is to distinguish them by adding a number followed after their name. Something like this:

Juan1

Or if there were 30252 records called Juan, put:

Juan30252

I did this but it does not go in registry order:

SELECT Paciente, COUNT(Paciente) AS Dupenumber 
            INTO #Cambiado
            FROM PS_GameData.dbo.Chars
            GROUP BY Paciente 
            HAVING ( COUNT(Paciente) > 1 )

            UPDATE PS_GameData.dbo.Chars
            SET Paciente = Paciente + CAST((BINARY_CHECKSUM(NEWID()))AS VARCHAR)  
            WHERE Paciente in (SELECT Paciente FROM #Cambiado)

            DROP Table #Cambiado

sql sql-server

asked by Juan Carlos Villamizar Alvarez 16.09.2016 в 06:05

source

1 answer

What is the asp.net equivalent of the "Execute" function of asp How to include an annotate field in filter Django?

score 1 · Accepted Answer

Assuming you are using SQL Server 2005+, you can use ROW_NUMBER :

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY Paciente ORDER BY Paciente),
            N = COUNT(*) OVER(PARTITION BY Paciente)
    FROM PS_GameData.dbo.Chars
)
UPDATE CTE
SET Paciente =  Paciente + CAST(RN AS VARCHAR(20)) 
WHERE N > 1;

Doing the query in this way avoids having to create a temporary table with duplicate patient records.