How to use case to construct a conditional update

I have to write an update query. If the special_member account is not cancelled then in the where clause I have to use this condition by adding a grace period of 15 days to the expiry date and compare it today’s date:

Convert(date,MEMBER_EXPIRY_DATE + 15) >= Convert(date,GETDATE())

If the membership is cancelled then I have to compare the actual expiry date with today’s date. This is my full query:

UPDATE SPECIAL_MEMBER SET SAVINGS_PERCENT = 10, ORDER_COUNT = 1 
WHERE SPECIAL_MEMBER = '4382' AND CASE WHEN (CANCELLED = 0) THEN  
Convert(date,MEMBER_EXPIRY_DATE + 15) >= Convert(date,GETDATE())
ELSE  (Convert(date,MEMBER_EXPIRY_DATE) >= Convert(date,GETDATE())) END

When I execute it I am getting:

Incorrect syntax near ‘>’.

Answer

Its a case expression, not a statement, as as such it can only return a value i.e. cannot contain any conditions. Just move the value you are comparing to outside the case e.g.

UPDATE SPECIAL_MEMBER SET
    SAVINGS_PERCENT = 10
    , ORDER_COUNT = 1 
WHERE SPECIAL_MEMBER = '4382'
AND CASE WHEN CANCELLED = 0
    THEN CONVERT(DATE,DATEADD(DAY,15,MEMBER_EXPIRY_DATE)) 
    ELSE CONVERT(DATE,MEMBER_EXPIRY_DATE) END >= CONVERT(DATE,GETDATE());

That however is not sargable i.e. cannot make use of any indexes on MEMBER_EXPIRY_DATE so I would recommend switching the logic around to

UPDATE SPECIAL_MEMBER SET
    SAVINGS_PERCENT = 10
    , ORDER_COUNT = 1 
WHERE SPECIAL_MEMBER = '4382'
AND MEMBER_EXPIRY_DATE >= CONVERT(DATE,DATEADD(DAY,CASE WHEN CANCELLED = 0 THEN -15 ELSE 0 END,GETDATE())) 

Notes:

• Dates don’t naturally add, use the dateadd function.
• There is nothing dynamic about this – dynamic SQL is something quite different
• Using a good layout and consistent casing makes a big difference in being able to read your SQL