Posted - 01/19/2009 : 19:38:30
| 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 inturn 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