June 13, 2011 at 1:58 pm
Hi all,
Any help is really apprecitaed.
create table outputdatavesrion (Id int, outputid Int, outputdataversionid int)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21233, 200012,1234)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21234, 200012,0)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21235, 200013,0)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21236, 200013,1235)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21233, 200013,1236)
I want to update the outputdatavesrionid's whose values are zero's based on the following condition.
If outputdataversionid is zero, update the value with the outputdatavesrionid of the previous row whose outputid equas to current row outputid.
If there is not previous versionid update the vesrion id with the row below whose outputid is same.
That is my result tables should be like the following
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21233, 200012,1234)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21234, 200012,1234)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21235, 200013,1235)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21236, 200013,1235)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21233, 200013,1236)
HOw can update the versionid's?
Many thanks in advance
June 13, 2011 at 2:19 pm
Hopefully this isn't a homework assignment... but what you want to do is usually done in two updates as follows:
update A set A.outputdataversionid = B.outputdataversionid
from outputdatavesrion A
JOIN outputdatavesrion B on A.OutputID = B.OutputID and A.Id = B.ID - 1
where A.outputdataversionid = 0
update A set A.outputdataversionid = B.outputdataversionid
from outputdatavesrion A
JOIN outputdatavesrion B on A.OutputID = B.OutputID and A.Id = B.ID + 1
where A.outputdataversionid = 0
The probability of survival is inversely proportional to the angle of arrival.
June 14, 2011 at 8:55 am
Thanks for the reply
what id Id is not sorted in an order.
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (21233, 200012,1234)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (212274, 200012,0)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (212353, 200013,0)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (213423, 200013,1235)
insert into outputdatavesrion(ID,OutputId,Outputdatavesrionid) values (121233, 200013,1236)
Please suggest
Thanks,
Deepthy
June 14, 2011 at 9:04 am
You mean if there are gaps in the IDs (they are not sequential) ? Your sample code indicated that they would be. However, that situation could be handled by using a correlated subquery which will work for either case:
update A set A.outputdataversionid = B.outputdataversionid
from outputdatavesrion A
JOIN outputdatavesrion B on A.OutputID = B.OutputID
and A.Id = (select min(ID) from outputdatavesrion where ID > A.ID)
where A.outputdataversionid = 0
update A set A.outputdataversionid = B.outputdataversionid
from outputdatavesrion A
JOIN outputdatavesrion B on A.OutputID = B.OutputID
and A.Id = (select max(ID) from outputdatavesrion where ID < A.ID)
where A.outputdataversionid = 0
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply