Update using IDENTITY function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Update using IDENTITY function?

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-02-03 : 17:07:31

Hi,
Is there a way to do Update using IDENTITY function?

I have a table whose ID column I am trying to update is based on order by clause of other column in the same table.

My sample table as below: Table1. This is just a sample table, the real table has more than 15 columns.

Col1…. .ID
------….-----
1…… null
7…….null
2…….null
17…….null
11…….null

I am trying to update the ID column by a number 1, 2, 3….an so on but the order needs to be in the ascending order of col1 data.

I am trying to achieve something like this.

Col1…. .ID
------….-----
1……..1
7…….3
2…….2
17…….4
11…….5

I tried something like below. I am trying to update the table.

Update table1
set ID = Identity(int,1,1)
from table1 t
order by t.col1

But get an error saying, Incorrect syntax near the keyword 'IDENTITY'. Is there a way to do Update using IDENTITY function?

Will appreciate any advice to achieve this without deleting the data in the table.

Thanks

Raj

TimS
Posting Yak Master

198 Posts

Posted - 2006-02-03 : 17:42:15

PK1 and PK2 are the Primary Key Columns on the table tablename

SELECT IDENTITY ( int , 1 , 1 ) AS tid, PK1, PK2
INTO #temp
FROM tablename
ORDER BY COL1, COL2

UPDATE x SET x.tid = t.tid
FROM tablename x
JOIN #temp t ON x.PK1 = t.PK1 AND x.PK2 = t.PK2

DROP TABLE #temp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 18:12:20

A select into won't always respect the order by clause (might be sevice pack dependent but I wouldn't rely on it).
You can insert into temp table with a cluserted index then select into from that - never seen that not work but still not guaranteed.

best to just calculate it
Update table1
set ID = (select count(*) from table1 t2 where t2.col1 <= t.col1)
from table1 t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-04 : 06:29:59

declare @id int
set @id = 0
update t2
set @id = t2.id= @id + 1
from (select top 100 percent col1
from table
order by col1) as t1
inner loop join table t2 on t2.col1 = t1.col1
option (force order)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 05:08:31

Also refer point 1 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

rkumar28
Starting Member

49 Posts

Posted - 2006-02-06 : 15:11:04

Hi,
Thank You so much for replying to this post. I tried the suggested code below. It works fine. Is there a way we can make it work for null values as well. I do have few nulls in col1. For nulls the @Id is getting update as nulls. I am sorry, I forgot to mention on this in my original post.

Any suggestions on how to fix this.

quote:
Originally posted by mmarovic

declare @id int
set @id = 0
update t2
set @id = t2.id= @id + 1
from (select top 100 percent col1
from table
order by col1) as t1
inner loop join table t2 on t2.col1 = t1.col1
option (force order)

Thanks
Raj

Raj

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 15:28:54

declare @id int
set @id = 0
update t2
set @id = t2.id= @id + 1
from (select top 100 percent col1
from table
order by col1) as t1
inner loop join table t2 on t2.col1 = t1.col1 or (t2.col1 is null and t1.col1 is null)
option (force order)

Not sure this will really give you what you want but give it a go.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-07 : 05:04:23

In that case make sure you have index on col1 (in ascending order):

declare @id int
set @id = 0
update t2
   set @id = t2.id= @id + 1
   from table t2 (index = idx_table_col1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources