Incremental (sequence) updation.

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

Transact-SQL (2000)

Incremental (sequence) updation.

Author

Topic

bobanjayan
Yak Posting Veteran

67 Posts

Posted - 2004-08-11 : 02:17:14

Hi,

I have a table with around 80000 rows.
It doesn't have a primary key. I want to create one.
How can I update this table it should update
with sequence from 100001
and increment by 1.

Please help.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-11 : 02:30:44

You can do this in Enterprise Manager. Simply open the table in design view, select the column in question and change the 'Identity' parameter to True, and then change the Identity Seed to 100001.

You can also do it using TSQL, but you will need to drop and re-create the table which is a bit long-winded given that EM does it for you...

Tim

bobanjayan
Yak Posting Veteran

67 Posts

Posted - 2004-08-11 : 03:10:15

Tim,
I tried that.
But it was giving error.
Now I got another method. And did properly. Because luckly I found unique values in MyTable.

-------------------------------------------------
select identity(int,100000,1) as Sequence, code, mcl_no into #x from MyTable
select * from #x

drop table #x

update MyTable
set new_code = #x.sequence
from MyTable,#x
where MyTable.code = #x.code
and MyTable.mcl_no = #x.mcl_no

-----------------------------------------------
Thanks a lot.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 05:11:57

you could also do this:

declare @counter int
set @counter = 100000 -- start value
UPDATE MyTable SET @counter = MyTableId = @counter + 1

Go with the flow & have fun! Else fight the flow :)

bobanjayan
Yak Posting Veteran

67 Posts

Posted - 2004-08-11 : 05:38:36

Thanks a lot Spirit1.
Excellent help. This was exactly what I wished for.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 05:53:36

no problem. it seems that this piece of sql code is very much in demand :))

Go with the flow & have fun! Else fight the flow :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-11 : 07:41:54

Consider this: if

quote:

select identity(int,100000,1) as Sequence, code, mcl_no into #x from MyTable
select * from #x

returns unique values per Sequence/Code/Mcl_no, then those combiniations of columns should be your primary key, not a random identity column. A primary key can consist of more than 1 column and should represent what combination of columns makes each row in your data unique. Thus, those columns are a perfect candidate for your primary key. Then, the database itself will ensure that your data remains valid and never voliates that rule.

I don't want to start a debate or anything, but just consider that argument. It's the entire basis of successful RDMS design.

- Jeff

bobanjayan
Yak Posting Veteran

67 Posts

Posted - 2004-08-12 : 06:31:28

Hi Jeff,
I don't want a composit primary key now. Just want to update the column with increment numbers. The above solution given by Spirit1 was sufficient for me.
Anyway thanks for your Alert.

tspratt
Starting Member

2 Posts

Posted - 2004-08-19 : 21:00:39

Hi, Guys.
I need a variation on this. I am sooo close, but I just can't quite put it together.

This solution:
Update MyTable SET @Counter = @Counter + 1, MyTable.Sequence = @Counter
Would work just fine, but I need to do the update in order.
I need an Order By Mytable.Sequence, but, of course Update doesn't take an order by clause.

I also have a select statement that works, but I don't know how to make it an update (I want to assign the RowNum value back to Sequence):
SELECT Id, Sequence,
RowNum=(Select Count(*) From Application t1
Where t1.ParentId = 'lt200408181816500477lapg'
and t1.Sequence<=Application.Sequence )*10
FROM Application
WHERE ParentId = 'lt200408181816500477lapg'
ORDER BY Application.Sequence

Thanks in advance!
Tracy

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-20 : 05:03:54

well, i think you need a #temp table here into which you inert the data in ordered state, update the column and insert everything into the original table.

of course you could use on-the-fly ranking which doesn't need an extra column in the table...

Go with the flow & have fun! Else fight the flow :)

tspratt
Starting Member

2 Posts

Posted - 2004-08-23 : 19:03:03

I hoped it wouldn't be complicated.

Maybe I'll just use indvidual updates from my procedural code.

Thanks!

Tracy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources