updating table with incremented value

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 2005 Forums

Transact-SQL (2005)

updating table with incremented value

Author

Topic

shabad
Starting Member

9 Posts

Posted - 2009-01-19 : 03:26:38

hi ,

I am trying to update a table with incremental value, and i need some help.

table columns are:
IDENTITY, ITEM, DESC, ID, SUBITEM

For occurance of each subitem i need the values for the Item from 1.

The table is already populated with ID as 1 , and it has many ITEM's which inuten has many SUBITEM's

I wrote the update statement but get the max(id) dint work , i think my code is not correct.

select @max_id = max(id) from Table1 where item = abcde
UPDATE TABLE1 SET id = @max_id+1 from Table1 where table1.item = abcde

This code updates the total occurances of each item for all the rows

Instead i need the output like the following :

ROW IETM ID SUBITEM
1 A 1 A1
2 A 2 A2
3 A 3 A3
4 A 4 A4
5 B 1 B1
6 B 2 B2
7 C 1 C1

In the current table the ID is all set to 1
can some one help me plz

Thanks in Advance
Shabad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 03:37:47

[code]
UPDATE t
SET t.ID=t.Seq
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Item Order BY SubItem) AS Seq
FROM Table
)t
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 03:42:40

Will not work. The exposed name ID is not available from the derived table.
Use this

UPDATE	t
SET	t.ID=t.Seq
FROM	(
		SELECT	ID,
			ROW_NUMBER() OVER (PARTITION BY Item Order BY SubItem) AS Seq
		FROM	Table1
	) AS t

E 12°55'05.63"
N 56°04'39.26"

shabad
Starting Member

9 Posts

Posted - 2009-01-19 : 13:37:35

Thanks for the help.

but when i try to run it says row_number() is not a recognized function name.

i am using sql server 2005 object explorer. the insertion and rest logic is written in a stored procedure.

plz let me know how to go abt in this issue, or ant other work round.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 14:55:58

TO use the new windowed functions available in SQL Server 2005, you must also set the compatibility level to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

shabad
Starting Member

9 Posts

Posted - 2009-01-19 : 16:48:35

I am new to SQL Server can u plz eloborate on this so that i can get this one done. Your help and time is really appricated

Thanks

shabad
Starting Member

9 Posts

Posted - 2009-01-19 : 17:02:42

I tried this one
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

but the msg says it can be 60/65/70/80.

The version of Sql server i am using is 2005
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Is this to do anything with the admin rights ???

Or is there any other alternative method to do this like adding a temp table or something. I did try that but seems not working

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-19 : 17:05:25

It sounds like you are running SQL Server 2000 but are using SQL Server 2005 client tools.

Please run SELECT @@VERSION and show us the exact output it returns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shabad
Starting Member

9 Posts

Posted - 2009-01-19 : 18:16:20

Thats right , i used the @@version and i am using sql server 2000 with 2005 tools may be thats the reason row_number() is not working.

But this still leaves the question where i started now , i need to update the table , need some help

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-19 : 21:40:19

quote:
Originally posted by shabad

Thats right , i used the @@version and i am using sql server 2000 with 2005 tools may be thats the reason row_number() is not working.

But this still leaves the question where i started now , i need to update the table , need some help

Thanks

You can simulate the row_number() function by modifying the code peso posted with something like this:

UPDATE t
SET t.ID=t.Seq
FROM (
SELECT
ID,
(select count(*) from Table1 t2 where t2.subitem <= t1.subitem and t2.item = t1.item ) AS Seq
FROM Table1 t1
) AS t

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-19 : 21:42:44

quote:
Originally posted by Peso

TO use the new windowed functions available in SQL Server 2005, you must also set the compatibility level to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

You don't need to. If the database is hosted on a SQL 2005 server, you can use the windowing functions even if the database compatibility level 80.

shabad
Starting Member

9 Posts

Posted - 2009-01-20 : 18:22:25

Thanks for the help but still i am having problems

i tried the sequence creation but it gives me the following error

UPDATE t
SET t.id=t.Seq
FROM (
SELECT
id,
(select count(*) from #Materials2 t2 where t2.t_sitm <= t1.t_sitm and t2.prev_item = t1.prev_item ) AS Seq
FROM #Materials2 t1
) AS t

Derived table 't' is not updatable because a column of the derived table is derived or constant.

Am i making any mistake in the query ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-20 : 20:57:25

SQL 2000 does not allow updating derived tables. SQL 2005 does.
You can update the table directly as:

update #Materials2
set id =
(
select count(*) from #Materials2 t2
where t2.t_sitm <= #Materials2.t_sitm
and t2.prev_item = #Materials2.prev_item )
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources