SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update Column based on previous row value..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cblatchford
Starting Member

United Kingdom
40 Posts

Posted - 01/31/2006 :  10:34:33  Show Profile  Visit cblatchford's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 01/31/2006 :  11:14:38  Show Profile  Reply with Quote
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

United Kingdom
40 Posts

Posted - 01/31/2006 :  11:36:56  Show Profile  Visit cblatchford's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 01/31/2006 :  13:09:34  Show Profile  Reply with Quote
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

United Kingdom
40 Posts

Posted - 02/01/2006 :  06:42:52  Show Profile  Visit cblatchford's Homepage  Reply with Quote
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

United Kingdom
3603 Posts

Posted - 02/01/2006 :  07:01:56  Show Profile  Reply with Quote
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

India
36 Posts

Posted - 02/01/2006 :  09:58:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000