CODEWITHSUNDEEP
sql-server-2008
imsudhni
imsudhni

Reputation: 1

How to update the first non null column of each row?

I have following solution 

Select Col1 = Case when Col1 is null then 'update'  
                    else Col1  
      ,Col2 = Case when Col1 is null then Col2  
                   when Col2 is null then 'update'  
                   else Col2  
      ,Col3 = Case when Col2 is null then Col3  
                   when Col3 is null then 'update'  
                   else Col3  
       .... and so on

Just wondering if anyone has better solution.

Upvotes: 0

Views: 30

Answers (1)

Alex Yu
Alex Yu

Reputation: 3547

Your solution is not correct.

SQL does not works this way, you need to check all preceding columns.

(And you forgot ENDs for your CASEs)

DECLARE @T TABLE(
    col1 sysname NULL
    ,col2 sysname NULL
    ,col3 sysname NULL
)

INSERT INTO @T 
SELECT NULL, 'N', NULL

-- Incorrect
Select Col1 = Case when Col1 is null then 'update'  
                    else Col1  
              END
      ,Col2 = Case when Col1 is null then Col2  
                   when Col2 is null then 'update'  
                   else Col2  
              END     

      ,Col3 = Case when Col2 is null then Col3  
                   when Col3 is null then 'update'  
                   else Col3  
              END
    FROM @T

-- Dull, but correct                
SELECT
    Col1 = ISNULL(Col1, 'update')
    ,col2 = CASE    
                WHEN Col1 IS NOT NULL
                        AND Col2 IS NULL 
                    THEN 'update'
                ELSE Col2
            END       
    ,col3 = CASE    
                WHEN Col1 IS NOT NULL 
                        AND Col2 IS NOT NULL
                        AND Col3 IS NULL
                    THEN 'update'
                ELSE Col3
            END
    FROM @T

Upvotes: 1

Related Questions