Join the result of a Temp column to a SQL Server table

Question

I am using SQL Server to create my database.

I want to add a column to my table that will calculate the number of NULL values ​​in each row, for example:

Column1 | Column2 | Column3 | Score a | B | C | 0 x | NULL | NULL | 2 

I currently have the following:

 Column1 | Column2 | Column3 a | B | C x | NULL | NULL 

I created a new column called Score, and to calculate it, I used:

 SELECT CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END As TMP FROM MyTable 

This returns a column with all my rows and a metric for each row:

  |TMP 1 |0 2 |2 

I would like to update the Score column in myTable with these values.

Thank you for your help.

Answer 1

You can use a computed column - a virtual column that is always computed with a given expression and is not saved to disk. This way you avoid data consistency issues. The syntax is simple:

 CREATE TABLE myTab ( column1 datatype , column2 datatype ... , Score AS CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END ); 

To modify an existing table and add such a column, use:

  ALTER TABLE myTab ADD Score AS CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END 

Source: https://msdn.microsoft.com/en-us/library/ms188300.aspx

Answer 2

It is usually a bad idea to store calculated values ​​depending on other columns. (Data redundancy, risk of data mismatch.) Create a view instead:

 create view MyView as SELECT column1, column2, column3, CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END As TMP FROM MyTable 

Answer 3

 DECLARE @temp TABLE ( Column1 CHAR(1), Column2 CHAR(1), Column3 CHAR(1), Score AS ( CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END) ) 

or

 CREATE VIEW dbo.vw_Table AS SELECT t.Column1 , t.Column2 , t.Column3 , Score = ( SELECT COUNT(t.val) FROM ( VALUES (t.Column1), (t.Column2), (t.Column3) ) t(val) ) FROM dbo.test t GO 

or

 ALTER TABLE dbo.tbl ADD Score AS ( CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END) PERSISTED GO 

Answer 4

 UPDATE mt SET Score = t.Tmp FROM MyTable mt INNER JOIN (SELECT idx, CASE WHEN Column1 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column2 IS NULL THEN 1 ELSE 0 END + CASE WHEN Column3 IS NULL THEN 1 ELSE 0 END FROM MyTable) t ON t.idx = mt.idx