Finding the least of three is working great, but how do I save the value to my table for each row?

DECLARE @LeastOf3 numeric

BEGIN TRAN

SELECT Item#, ID, Market, LiFo, Wgtd_Avg,
(
    CASE
        WHEN Market = Wgtd_Avg and Wgtd_Avg = LiFo THEN Market

        WHEN Lifo = 0 and Wgtd_Avg = 0 and Market <> 0 THEN Market
        WHEN Market < LiFo AND Market < Wgtd_Avg AND Market <> 0 THEN Market
        WHEN Market <= Wgtd_Avg AND LiFo = 0 THEN Market
        WHEN Market <= LiFo  AND Wgtd_Avg = 0 THEN Market

        WHEN Market = 0 and Wgtd_Avg = 0 and LiFo <> 0 THEN LiFo
        WHEN LiFo < Market AND LiFo < Wgtd_Avg AND LiFo <> 0 THEN LiFo
        WHEN LiFo <= Market  AND Wgtd_Avg = 0 THEN LiFo
        WHEN LiFo <= Wgtd_Avg  AND Market = 0 THEN LiFo

        WHEN Market = 0 and LiFo = 0 and Wgtd_Avg <> 0 THEN Wgtd_Avg
        WHEN Wgtd_Avg < Market and Wgtd_Avg < LiFo and Wgtd_Avg <> 0 THEN Wgtd_Avg
        WHEN Wgtd_Avg <= Market AND LiFo = 0 THEN Wgtd_Avg
        WHEN Wgtd_Avg <= LiFo AND Market = 0 THEN Wgtd_Avg

        WHEN Market <= LiFo and LiFo < Wgtd_Avg  and Market <> 0 THEN Market
        WHEN LiFo <= Market and Market < Wgtd_Avg  and LiFo <> 0 THEN LiFo
        WHEN Wgtd_Avg <开发者_如何转开发= LiFo and LiFo < Market and Wgtd_Avg <> 0  THEN Wgtd_Avg

        ELSE 0
    END
) AS LeastOf3
FROM VF_CasINV_Cost
where (CalendarYear = 2010) and (Item# < 99999990)
--SET LeastOfThree = LeastOf3


--UPDATE VF_CasINV_Cost
--SET LeastOfThree = @LeastOf3
--where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

--go

use an UPDATE FROM statement, sort of like this:

UPDATE VF_CasINV_Cost 
SET LeastOfThree = LeastOf3
FROM
SELECT (... your big select statement...)

You just need to include your CASE in the UPDATE statement and not the local variable you were trying to use, like:

UPDATE VF_CasINV_Cost
SET LeastOfThree = --<<your big case here
where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

so this is the final code:

UPDATE VF_CasINV_Cost
SET LeastOfThree = CASE
                       WHEN Market = Wgtd_Avg and Wgtd_Avg = LiFo THEN Market

                       WHEN Lifo = 0 and Wgtd_Avg = 0 and Market <> 0 THEN Market
                       WHEN Market < LiFo AND Market < Wgtd_Avg AND Market <> 0 THEN Market
                       WHEN Market <= Wgtd_Avg AND LiFo = 0 THEN Market
                       WHEN Market <= LiFo  AND Wgtd_Avg = 0 THEN Market

                       WHEN Market = 0 and Wgtd_Avg = 0 and LiFo <> 0 THEN LiFo
                       WHEN LiFo < Market AND LiFo < Wgtd_Avg AND LiFo <> 0 THEN LiFo
                       WHEN LiFo <= Market  AND Wgtd_Avg = 0 THEN LiFo
                       WHEN LiFo <= Wgtd_Avg  AND Market = 0 THEN LiFo

                       WHEN Market = 0 and LiFo = 0 and Wgtd_Avg <> 0 THEN Wgtd_Avg
                       WHEN Wgtd_Avg < Market and Wgtd_Avg < LiFo and Wgtd_Avg <> 0 THEN Wgtd_Avg
                       WHEN Wgtd_Avg <= Market AND LiFo = 0 THEN Wgtd_Avg
                       WHEN Wgtd_Avg <= LiFo AND Market = 0 THEN Wgtd_Avg

                       WHEN Market <= LiFo and LiFo < Wgtd_Avg  and Market <> 0 THEN Market
                       WHEN LiFo <= Market and Market < Wgtd_Avg  and LiFo <> 0 THEN LiFo
                       WHEN Wgtd_Avg <= LiFo and LiFo < Market and Wgtd_Avg <> 0  THEN Wgtd_Avg

                       ELSE 0
                   END
where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

I tried to make your statement shorter

UPDATE VF_CasINV_Cost SET LeastOfThree =
(select coalesce(min(low), 0) from 
(select market low where market > 0 union all
select wgtd where wgtd > 0 union all
select lifo where lifo > 0 union all select null) a
)
WHERE CalendarYear = 2010

This proves it works

declare @t table (LeastOfThree int, market int, wgtd int, lifo int)

insert @t values (null, 1,2,0)

UPDATE @t SET LeastOfThree =
(select coalesce(min(low), 0) from 
(select market low where market > 0 union all
select wgtd where wgtd > 0 union all
select lifo where lifo > 0 union all select null) a
)
select * from @t