March 26, 2008 at 8:57 pm
car_no.....rev_no.....color......weight
123..........0............white.....20000
123..........1............white.....22000
123..........2..........._____________
I want to update the color and weight for car_no 123, rev_no 2 with the values for car_no 123, rev_no 1. Can I do this in a single update statement or do I need to get the values for color and weight first and then do an update?
March 26, 2008 at 9:34 pm
You can do something like this. Note you can change the update to a select statement to confirm that the data you are updating is correct. I have tested it with the below small sample.
declare @t table(
car_no int,
rev_no int,
color varchar(10),
weight int
)
insert into @t
select 123,0,'white',20000 union all
select 123,1,'white',22000 union all
select 123,2,'red',22000 union all
select 456,1,'blue',25000
update t1
set color = t2.Color,
weight = t2.weight
from @t t1
inner join @t t2
on t1.car_no = t2.car_no
where (t1.car_no = 123 and t1.rev_no = 2) and
(t2.car_no = 123 and t2.rev_no = 1)
March 26, 2008 at 10:10 pm
Thank you so much for heling me out - I will give it a try.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply