updated column based on previous row

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)

updated column based on previous row

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2009-04-07 : 10:42:39

I need to run a query to updated the field values based on the audit values. Here are the sample below...this is working fine, except it is a bit slow. Any suggestion on how to re-write this stuffs.

thanks

------------------------

DECLARE @t TABLE(
id INT,audit_date DATETIME,audit_action CHAR(1),audit_groupid INT,audit_values_old VARCHAR(100),audit_values_new VARCHAR(100)
)

INSERT INTO @t
(id,audit_date,audit_action,audit_groupid,audit_values_old)
SELECT 1,'01/21/09','U',68,' TRUST - SUB ACCOU'
UNION ALL
SELECT 2,'04/03/09','U',68,' TRUST - SUB ACCOUNT '
UNION ALL
SELECT 3,'01/21/09','U',268,'TRUST MID ACCT'
UNION ALL
SELECT 4,'04/03/09','U',268,'TRUST MID CAP ACCT '
UNION ALL
SELECT 5,'10/30/08','D',440,'RIQUEZ '
UNION ALL
SELECT 6,'04/03/09','U',440,'ADCHAM'
UNION ALL
SELECT 7,'10/30/08','U',441,'SLAK'
UNION ALL
SELECT 8,'01/22/09','U',441,'SASSO'
UNION ALL
SELECT 9,'12/02/08','U',442,' '
UNION ALL
SELECT 10,'12/23/08','U',442,'FRANK'
UNION ALL
SELECT 11,'12/23/08','U',442,'FRANK JR'
UNION ALL
SELECT 12,'01/22/09','U',442,'FRANK JR III'

SELECT * FROM @t

declare @id int
declare @id2 int

set @id=1
set @id2=(select max(id) from @t)

while @id <= @id2
begin

update T set audit_values_new = (Select audit_values_old from @t X where X.id = @id+1)
from @t T where T.id=@id
and t.audit_groupid=(Select audit_groupid from @t X where X.id = @id+1)

set @id=@id+1
end

SELECT * FROM @t2

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 11:17:04

why use a loop ?

try this,

update a
set 
a.audit_values_new= b.audit_values_old 
from @t a left join @t b on a.id=b.id-1 and a.audit_groupid=b.audit_groupid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:28:28

if id values are not continuos


update a
set 
a.audit_values_new= (select top 1 audit_values_old from @t where id >a.id and audit_groupid=a.audit_groupid order by id)
from @t a

hai
Yak Posting Veteran

84 Posts

Posted - 2009-04-07 : 11:34:55

thanks very much...

quote:
why use a loop ? --Old habit's from coding in VB and still learning sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources