Update filed ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update filed ?

Author

Topic

sasan_vm
Yak Posting Veteran

51 Posts

Posted - 2008-04-22 : 04:16:10

Hello,

i have a table with million record, an bigint filed like:

ID Name
-------------------------------------
1 Adam
4 Bob
5 Daivid
100 ...

Also i have unique index on filed ID, i want update this filed for any row to get row number and to be like:

ID Name
-------------------------------------
1 Adam
2 Bob
3 Daivid
4 ...

Can any one show me sample code ?

Kind Regards,
sasan.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 04:22:31

UPDATE f
SET f.ID = f.RecID
FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RecID FROM Table) AS f

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 04:37:39

If not using SQL 2005 or compatibility level is below 90 use:-

UPDATE f
SET f.ID = f.RecID
FROM (SELECT t.ID, 
(SELECT Count(*) + 1 
FROM Table 
WHERE ID <t.ID) AS RecID
 FROM Table t) AS f

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 04:52:57

And for 1 million records, how long will that update take?

Your update is a "triangular join", and involves (1M * 1M / 2) records, which is 500,000,000,000 (500 billion records).

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 04:56:10

If not using SQL Server 2005, you should use this

SELECT		ID AS ID_old,
		IDENTITY(INT, 1, 1) AS ID_new
INTO		#Temp
FROM		OneMillionRecordTable
ORDER BY	ID

UPDATE		omrt
SET		omrt.ID = t.ID_new
FROM		OneMillionRecordTable AS omrt
INNER JOIN	#Temp AS t ON t.ID_old = omrt.ID

DROP TABLE #Temp

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 05:33:11

But serial number really matters only if you want to show in the front end application

Madhivanan

Failing to plan is Planning to fail

sasan_vm
Yak Posting Veteran

51 Posts

Posted - 2008-04-22 : 07:20:09

quote:
Originally posted by Peso

If not using SQL Server 2005, you should use this
SELECT ID AS ID_old,
IDENTITY(INT, 1, 1) AS ID_new
INTO #Temp
FROM OneMillionRecordTable
ORDER BY ID

UPDATE omrt
SET omrt.ID = t.ID_new
FROM OneMillionRecordTable AS omrt
INNER JOIN #Temp AS t ON t.ID_old = omrt.ID

DROP TABLE #Temp

E 12°55'05.25"
N 56°04'39.16"

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources