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)
 Update Column based on previous row value..

Author  Topic 

cblatchford
Starting Member

40 Posts

Posted - 2006-01-31 : 10:34:33
Hi,

Just a quicky hopefully; how can I update a value in my table based on the previous table value? Currently the table looks like..

TABLE
1
1
1
1
1

And I want it to look like

TABLE
1
2
3
4
5

I want to update each field based on the value of the prior field + 1. There are around 20,000 rows I believe..

Cheers..

CB

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-31 : 11:14:38
No no no no no....
First, tell us WHY you want to do this, before we all go off and propose half a dozen solutions that perfectly fit the problem as you described it, but which are completely inadequate for the problem you have.
Go to Top of Page

cblatchford
Starting Member

40 Posts

Posted - 2006-01-31 : 11:36:56
lol!

OK I have a front end helpdesk application which I need to populate with user emails. The user details are already in the system, so I need to add the email details to them through SQL (saves going through each record and updating one by one). The structure of the helpdesk software sql tables is such that the clients table has a primary key ID which is reference in another table named entityvalues. The entityvalues table has a separate primary key id, as the entityvalues table holds information on everything, client site, asset details, and email details.

So I extracted the usernames from our HR database, did an update on the table to create the email address's (firstname.lastname@comapany.com), now I need to apply a primary key id to each row. So I've taken the last primary key id in the entityvalues table, entered it into the new table I created, now all I need to do is create a new pkid for each row, which is what I'm after.

By the way I've sussed it out now anyway, but thanks for the helpful response.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-31 : 13:09:34
I guess I don't understand why you aren't just using IDENTITY when you initially populate the table.
Go to Top of Page

cblatchford
Starting Member

40 Posts

Posted - 2006-02-01 : 06:42:52
Sorry I don't understand where your coming from. I was simply looking for a quick fix to a problem; the whole point being that I wouldn't have to explain the entire issue..!!

Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-01 : 07:01:56
What he means is just add an IDENTITY field to your table..


create table a (
field1 int identity (1,1),
email varchar(100)
)

insert into a select 'blah@blah.com'
union select 'blah@blah.com'
union select 'blah@blah.com'

select * from a
Go to Top of Page

Jothikannan
Starting Member

36 Posts

Posted - 2006-02-01 : 09:58:44
i think u r looking Like this....

declare @variable int
select @variable=1

update my_Table
set field=@variable,
@variable=@variable+1
Go to Top of Page
   

- Advertisement -