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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update using IDENTITY function?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rkumar28
Starting Member

USA
49 Posts

Posted - 02/03/2006 :  17:07:31  Show Profile  Reply with Quote
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

Edited by - rkumar28 on 02/03/2006 17:08:06

TimS
Posting Yak Master

USA
198 Posts

Posted - 02/03/2006 :  17:42:15  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/03/2006 :  18:12:20  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 02/04/2006 :  06:29:59  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 02/06/2006 :  05:08:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer point 1 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rkumar28
Starting Member

USA
49 Posts

Posted - 02/06/2006 :  15:11:04  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2006 :  15:28:54  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 02/07/2006 :  05:04:23  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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)

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000