CODEWITHSUNDEEP
sql-server
PS078
PS078

Reputation: 461

TSQL: Strange behavior using case statement

I have a scenario wherein I have to update Customer table’s Billing_Start_Month column based on the Start_Month.

I have been given Start_Month value in an excel sheet along with customer name. To do so, I have created a temp table and inserted all CustomerName and Start_Month values. In excel sheet Start_Month values contain some other values also eg: (If Start_Month is any month like January, February etc then update Billing_Start_Month column as 1, 2 recpectively, but if Start_Month values contains values like 1,3,4,5,7 then we need to leave those records without updating Billing_Start_Month column of customer table). We have only 5 records in excel sheet which has Start_Month values like “1,3,4,5,7”.

Now I am updating Customer table’s Billing_Start_Month column using following query:

UPDATE Customer
SET Billing_Start_Month = CASE WHEN tmp.StartMonth = 'January' THEN 1
                               WHEN tmp.StartMonth = ‘February’ THEN 2
                          END
FROM #temp tmp
INNER JOIN Customer c
ON tmp.Customer = c.acc_name

But on executing this query I am getting Check Constraint fail error. And when I have deleted records of Start_Month values containing “1,3,4,5,7” from temp table, the above query executed successfully.

Simillarly, in case I am not deleting records of Start_Month values containing “1,3,4,5,7” from temp table then I have to include another when part in case like this,

CASE WHEN tmp.StartMonth = 'January' THEN 1 
     WHEN tmp.StartMonth = ‘February’ THEN 2
     WHEN tmp.StartMonth = ‘1,3,4,5,7’ THEN 1235 –- any random value and not null
END

Please help me understanding this behavior of case statement. I am assuming that, When I am not including case ‘1,3,4,5,7’ in case statement the those 5 records are not considered in update statement.

Upvotes: 1

Views: 96

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You can try using the following query. I have added an ELSE condition to your CASE statement which simply uses the Billing_Start_Month in case the month be not January or February (in other words, it does not change the record in this case).

UPDATE Customer
SET Billing_Start_Month = CASE WHEN tmp.StartMonth = 'January' THEN 1
                               WHEN tmp.StartMonth = 'February' THEN 2
                               ELSE Billing_Start_Month
                          END
FROM #temp tmp
INNER JOIN Customer c
ON tmp.Customer = c.acc_name

Upvotes: 4

David Hedlund
David Hedlund

Reputation: 129792

Your UPDATE statement tries to update all records. From your description, it sounds like there are some scenarios where none of your CASE conditions will yield anything, and you have no else. You could do ELSE Billing_Start_Month to simply yield the currenct valye for records that don't match the cases.

Alternatively, you could add a WHERE to your update, so as to only update the rows for which Start_Month is January or February.

Upvotes: 4

Related Questions