CODEWITHSUNDEEP
sqlsql-server
SanfordB
SanfordB

Reputation: 1

Changing Duplicate ID's in a SQL Table

I have duplicate ID's in a column that should be unique. I want to run an update query to edit ONLY the second occurrence of the ID Not all of them.

For example - If I run this:

SELECT *
FROM [dbo].[Time_Frame]
WHERE [Respondent ID: Respondent] = 283028

I get this:

Respondent ID: Respondent YEAR QUARTER

How do I only edit the second row ID say from 283028 to 28302899 so that i can ensure I still have the same number of rows in my table, but all IDs are unique?

I have 837 duplicates in my table. When I try to update it will update 1674 rows rather than just the 837 that are duplicate ID's.

Thanks in advance for any help you might be able to offer me!

Upvotes: 0

Views: 727

Answers (2)

Him
Him

Reputation: 19

I think the below query should work .

            CREATE TABLE TIME_FRAME 

            (
            ID INT ,
            YEAR INT ,
            QTR VARCHAR(100) 
            )


            INSERT INTO TIME_FRAME 
            SELECT 283028,2013,'Q1 2013'
            INSERT INTO TIME_FRAME 
            SELECT 283028,2015,'Q3 2015'
            INSERT INTO TIME_FRAME 
            SELECT 283029,2013,'Q1 2013'
            INSERT INTO TIME_FRAME 
            SELECT 283029,2015,'Q3 2015'
            INSERT INTO TIME_FRAME 
            SELECT 283030,2013,'Q1 2013'
            INSERT INTO TIME_FRAME 
            SELECT 283030,2015,'Q3 2015'

            CREATE TABLE #TEMP 
            (
            TEMPID INT IDENTITY(1,1),
            ID INT ,
            YEAR INT ,
            QTR VARCHAR(100) 

            )


            INSERT INTO #TEMP(ID , YEAR,QTR) 
            SELECT * FROM TIME_FRAME


            BEGIN TRAN 

            UPDATE T 
            SET T.ID = TT.ID*100 -1 
            FROM TIME_FRAME T
            JOIN #TEMP TT 
            ON T.ID = TT.ID 
            AND T.QTR = TT.QTR
            AND T.YEAR = TT.YEAR
            AND TT.TEMPID IN (SELECT MAX(TEMPID) FROM #TEMP
            GROUP BY ID)

            SELECT * FROM TIME_FRAME

            --ROLLBACK TRAN

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this using updatable CTEs:

with toupdate as (
      select tf.*,
             row_number() over (partition by [Respondent ID: Respondent] order by year, quarter) as seqnum
      from [dbo].[Time_Frame] tf
     )
update toupdate
    set [Respondent ID: Respondent] = [Respondent ID: Respondent]*1000 + seqnum
    where seqnum > 1;

This is more general than your approach. It enumerates duplicate keys so the result is that the keys are unique (assuming no more than 998 dupliates per key).

Upvotes: 1

Related Questions