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

Question

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 issue with the sql?

Andomar · Accepted Answer · 2010-04-24 17:03:18Z

4

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

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

answered Apr 24, 2010 at 17:03

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

systempuntoout · Accepted Answer · 2010-04-24 17:05:10Z

2

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

answered Apr 24, 2010 at 17:05

systempuntoout

74.6k47 gold badges176 silver badges247 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related