What's wrong with this UPDATE FROM using a case statement?

update p
set p.storePrice = 
    CASE 
       WHEN p.costPrice BETWEEN 0.00 AND 1.00 
       THEN p.costPrice * 1.0
    CASE 
       WHEN p.costPrice BETWEEN 0.00 AND 1.00 
       THEN p.costPrice * 1.0
    ELSE 
       p.msrpPrice
    END
FROM product p                      
WHERE p.type = 1

The error says:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'CASE'.

I can't seem to see any 开发者_Python百科issue with the sql?

There are two starting case's. Remove the second one, like:

case 
    when x=1 then ...
    when x=2 then ...
end

Try with:

UPDATE p
    SET 
     p.storePrice = 
            CASE 
                WHEN 
                    p.costPrice BETWEEN 0.00 AND 1.00 
                    THEN p.costPrice * 1.0

                WHEN 
                    p.costPrice BETWEEN 0.00 AND 1.00 
                    THEN p.costPrice * 1.0                              

                ELSE 
                    p.msrpPrice
            END
FROM product p                    
WHERE p.type = 1