April 30, 2008 at 2:01 pm
I am converting a legacy data file into a relational database (please do not suggest a redesign). The legacy file has an error. There are two key fields. PropNum and SeqNum. The legacy application did not increment the sequence number, so my database is filled with duplicate keys. All the SeqNum values are 1, but they should increment from 1 to n for each PropNum
current
Propnum Seqnum
12 1
12 1
...
12 1
15 1
15 1
15 1
Desired
12 1
12 2
...
12 6
15 1
15 2
15 3
Thanks!
April 30, 2008 at 3:06 pm
First we place a unique field on your table. This allows us to join it to itself for the update. We use ROW_NUMBER and PARTITION BY to get the numbers/results you want and then update from there. finally dropping the unneeded column.
ALTER TABLE yourTable ADD nDex INT IDENTITY(1,1) NOT NULL
UPDATE yourTable
SET seqNum = rn
FROM
yourTable AS a
INNER JOIN (SELECT nDex, ROW_NUMBER() OVER (PARTITION BY propNum ORDER BY propNum) AS rn FROM yourTable) AS b
ON a.nDex = b.nDex
ALTER TABLE yourTable DROP COLUMN nDex
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 30, 2008 at 3:50 pm
Thanks it worked. I left out a few things, but I got them resolved. The records were in certain order and I knew which order, so I added a LINEAGE_ID to the ORDER BY. I also had the current record, which SEQNUM was 0, so I just put that in the AND in the join. I left out those details because I didn't want to confuse anyone and I am adding them in my reply in case someone else has a similar situation.
ALTER TABLE yourTable ADD nDex INT IDENTITY(1,1) NOT NULL
UPDATE yourTable
SET seqNum = rn
FROM
yourTable AS a
INNER JOIN (SELECT nDex, ROW_NUMBER() OVER (PARTITION BY propNum ORDER BY propNum, LINEAGE_ID) AS rn FROM yourTable) AS b
ON a.nDex = b.nDex
AND seqNum > 0
ALTER TABLE yourTable DROP COLUMN nDex
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply