May 27, 2008 at 1:13 pm
I am trying to update a column without using cursors/loops.On the contrary I would like to use a set based query.Any help would be greatly appreciated .
Here is some sample data.
Create table #Detail
(
Code int,
SetNo nvarchar(10),
CylNo nvarchar(10),
Stage int
)
Insert into #Detail (Code,SetNo,CylNo)
select 1000,1,'1a'
union all
select 1000,1,'1b'
union all
select 1000,1,'1c'
union all
select 1000,1,'1d'
union all
select 1000,1,'1e'
union all
select 1000,2,'2a'
union all
select 1000,2,'2b'
union all
select 1000,2,'2c'
union all
select 1000,2,'2d'
union all
select 1000,2,'2e'
--select * from #Detail
Create table #Summary
(Code int,
NumPrelim int,
DaysPrelim int,
NumberFinal int,
DaysFinal int
)
Insert into #Summary
SELECT 1000,2,7,3,28
--select * from #Summary
Basically I would like to update the column Stage
with DaysPrelim (2 rows as NumPrelim=2) and DaysFinal(3 rows as DaysFinal=28)
for each SetNo
In other words..final output should look like
Code SetNo CylNo Stage
----------- ---------- ---------- -----------
1000 1 1a 2
1000 1 1b 2
1000 1 1c 28
1000 1 1d 28
1000 1 1e 28
1000 2 2a 2
1000 2 2b 2
1000 2 2c 28
1000 2 2d 28
1000 2 2e 28
Thank you...
May 28, 2008 at 1:13 am
Your description of the problem is missing any rules there might be for assigning the value of Stage based on CylNo.
However, if the first two values of Stage in each SetNo ordered by CylNo are always set to NumPrelim, and the remainder set to DaysFinal, then this should work:
;WITH myCTE
AS
(SELECT d.Code
,d.SetNo
,d.CylNo
,s.NumPrelim
,s.DaysFinal
,ROW_NUMBER() OVER (PARTITION BY SetNo
ORDER BY CylNo) AS rn
FROM #Detail d
JOIN #Summary s
ON s.Code = d.Code
)
UPDATE d
SET Stage = CASE WHEN rn <= 2
THEN NumPrelim
ELSE DaysFinal
END
FROM myCTE AS c
JOIN #Detail AS d
ON d.Code = c.Code
AND d.SetNo = c.SetNo
AND d.CylNo = c.CylNo
May 28, 2008 at 7:12 am
That's fantastic...Thank you so much for your help.:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply