CODEWITHSUNDEEP
sqlsql-updateduplicates
Stanton
Stanton

Reputation: 1374

Use tSQL to sequentially increment value based on other values

I'm almost sure this is impossible, but I've been amazed so often at the power of the SQL language that I just thought I'd ask, because this would save me weeks of work.

Let's say you have a table like this, with random numbers of duplicate records:

First   Last    Title   Grade   Replicate
Mike    Smith   Manager 2          1
Jenn    Jones   Sales   1          1
Bill    Bennett CEO 2   1          1
Jeff    Miller  Sales   4          1
Harry   James   Manager 6          1
Beth    Samuals Dock    1          1
Cathy   Gordon  Sales   2          1
Bill    Bennett CEO 2   1          1
Jeff    Miller  Sales   4          1
Harry   James   Manager 6          1
Beth    Samuals Dock    1          1
Cathy   Gordon  Sales   2          1
Bill    Bennett CEO 2   1          1
Jeff    Miller  Sales   4          1
Harry   James   Manager 6          1
Jeff    Miller  Sales   4          1
Harry   James   Manager 6          1

The 'Replicate' field is there to make these records unique. The replicates need to be incremented by one each time a new duplicate is found, so that the other records can be duplicated but the Replicate field will contain 1, 2, 3.... and so on.

This is a simplified example of my problem - about 40,000 records in a table with about 30 fields, and 16 fields that must be made unique by using the 'Replicate' field. Sorting them on the 16 'uniqueness' fields is easy, of course. But is there any way to tell SQL to update the Replicate field, adding one each time a duplicate is found?

As I said, I suspect that this isn't possible or that if it is, that the solution is above my skill level, but I've been wrong about that before.

Thanks.

Upvotes: 0

Views: 126

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can use ROW_NUMBER() for this:

;WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY [First],[Last],[Title],[Grade] ORDER BY Replicate) AS UPD_Replicate
              FROM Table1
              )
SELECT *
FROM cte

Demo: SQL Fiddle

Or to UPDATE the field:

;WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY [First],[Last],[Title],[Grade] ORDER BY Replicate) AS UPD_Replicate
              FROM Table1
              )
UPDATE cte
SET Replicate = UPD_Replicate

The ROW_NUMBER() function assigns a number to each row. PARTITION BY is optional, but used to start the numbering over for each value in a given group, ie: if you PARTITION BY Some_Date then for each unique date value the numbering would start over at 1. ORDER BY of course is used to define how the counting should go, and is required in the ROW_NUMBER() function.

Upvotes: 1

Related Questions