Update to previous value

Question

Post reply

Update to previous value

Donald Sawford

SSCrazy

Points: 2778
More actions
April 2, 2004 at 12:37 pm

#152103

Hi,
Any assistance that you can offer me would be very appreciated. Given the following table and data, I wish to update the Sep field of all records where IsSlate = 0. The records where IsSlate = 1 are correct, but Sep of subsequent records should = the last correct Sep when ordered by SubjectID.
Create Table test(
SubjectID char(10) unique Not Null,
Sep int,
IsSlate bit)
insert into test values('000001', 1, 1)
insert into test values('000002', 7, 0)
insert into test values('000003', 6, 0)
insert into test values('000004', 2, 1)
insert into test values('000005', 1, 0)
insert into test values('000006', 3, 1)
The updated table should look like:
SubjectID Sep       IsSlate
---------- -------- -------
000001     1           1
000002     1           0
000003     1           0
000004     2           1
000005     2           0
000006     3           1
What is the best way to accomplish this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Is the SubjectID going to be a character field or is it going to be numeric?

Shamless self promotion - read my blog http://sirsql.net

Donald Sawford SSCrazy Points: 2778 More actions · Answer 2

SubjectID is char, but will always contain numerical values, so can successfully be cast as int.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Changing the data type for the subjectid to an int you could use the following. It's not the most elegant, I am sure that there are a lot better ways to do this, but it works. I'm not sure if you want to update the isslate column at the same time as you update the sep, if needs be you can just add that to the final update.

Create Table test(

SubjectID int unique Not Null,

Sep int,

IsSlate bit)

insert into test values('000001', 1, 1)

insert into test values('000002', 7, 0)

insert into test values('000003', 6, 0)

insert into test values('000004', 2, 1)

insert into test values('000005', 1, 0)

insert into test values('000006', 3, 1)

/* Begin update */

set nocount on

declare @newsep int, @newsubid char(10), @ident char(10), @newseb int

declare @subject table (subid char(10), prevsubid char(10), newsep int)

insert into @subject(subid) select subjectid from test where isslate = 0

select @ident = min(subid) from @subject where subid > 0

while @ident is not null

begin

select @newsubid = max(subjectid) from test t, @subject s where t.subjectid < @ident and t.isslate != 0

select @newsep = sep from test where subjectid = @newsubid

update @subject set prevsubid = @newsubid, newsep = @newsep where @ident = subid

select @ident = min(subid) from @subject where subid > 0 and subid > @ident

end

--select * from @subject

update test

set sep = newsep

from @subject s inner join test t on s.subid = t.subjectid

Shamless self promotion - read my blog http://sirsql.net

Donald Sawford SSCrazy Points: 2778 More actions · Answer 4

Thanks, stacenic

I think that this will do what I want it to do. It worked on the test data, so I think I can adapt it work on the real data. You have been a great help.

Thanks again