Home SQL code toggles between the value 1 and NULL
Reply: 1

SQL code toggles between the value 1 and NULL

Osye Pritchett
Osye Pritchett Published in 2018-02-13 23:53:00Z
UPDATE [MyDatabase].[dbo].[MyTable1]
SET [MyTable1].[Column To Be Updated] = 
        CASE WHEN [MyTable1].[Column To Be Updated] IS NULL THEN 1
                 WHEN [MyTable1].[Column To Be Updated] = 0 THEN 1
FROM [MyDatabase].[dbo].[MyTable1]
INNER JOIN [MyDatabase].[dbo].[MyTable2] mt2 WITH(NOLOCK)
ON mt2.[AN ID] = [MyDatabase].[dbo].[MyTable1].[AN ID]
WHERE dm.[ANOTHER ID] = '1234'

This column can contain NULL, 0, 1, 2, or 3.

When I run this SQL it changes NULL, 0, 2, and 3 to 1 and it changes 1 to NULL. If I run it again it changes 1 to NULL and NULL to 1. (This is T-SQL in SQL Server 11.)

I am confused as to why it is exhibiting this behavior. Any help would be appreciated.

digital.aaron Reply to 2018-02-14 00:02:44Z

The reason it's alternating is because of your CASE statement. There is no ELSE nor DEFAULT clause, so when the column doesn't match any of your conditions, the value is set to NULL. When the update runs again, NULL is a case that's handled, so the value gets set to 1.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.325326 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO