How to update multiple fields of a table with data from another table

Question

I am working on SQL Server 2019. I have two tables, one has data about the cities and the sales representatives' visited numbers stored in Col1 to Col6. And another one has all the deleted values from this table. This table has about 5000 rows. I have to restore the deleted Col values from the backup table. For example, if a row has a null value in Col4 but int values in Col5 and Col6, I have to update this row with the Col4 value from the backup table of the corresponding row as identified by the ID value. In the enclosed screenshot, Col3 of Row1, Col5 of Row2, and Col1 of Row 4 will be updated with the corresponding Col values from the backup table while Row 3 will remain unchanged as the last Col is null. I am new to writing complex SQL queries. 

So far I came up with this script.

    UPDATE p
    SET 
    --p.Col1 = isnull(p.Col1, a.Col1),
    --p.Col2 = isnull(p.Col2, a.Col2),
    --p.Col3 = isnull(p.Col3, a.Col3),
    --p.Col4 = isnull(p.Col4, a.Col4),
    --p.Col5 = isnull(p.Col5, a.Col5),
    --p.Col6 = isnull(p.Col6, a.Col6)
    p.Col4 = a.Col4
    FROM Reps p
    INNER JOIN
    Deleted a
    ON p.ID = a.ID 
    WHERE 
    a.Col4 is not null
    AND p.Col4 is null
This works when Col4 is null. So, I have to write the same SQL six times, one each for each Col. I thought of using the IsNull function as shown in the commented-out section of the code. When I tried it, only the last col6 is updated but not Col1 to 5!

    ID  Name          Col1   Col2   Col3   Col4  Col5   Col6
    --- ----------------------------- ----------- -----------  
    1   Houston        5     200    NULL   85    200    20
    2   Dallas         12    45     890    22    NULL   4
    3   San Antonio    34    38     222    3     25     NULL
    4   Temple         NULL  567    8      90    23     1

The following is the Create Script for the destination table:

CREATE TABLE [dbo].[Projection](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Col1] [int] NULL,
	[Col2] [int] NULL,
	[Col3] [int] NULL,
	[Col4] [int] NULL,
	[Col5] [int] NULL,
	[Col6] [int] NULL,
 CONSTRAINT [PK_Projection] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

Hi @Sherpa

Try this query:

;WITH CTE AS
(
 SELECT P.ID,P.Name,P.Col1,P.Col2,P.Col3,P.Col4,P.Col5
       ,CASE WHEN P.Col1 IS NULL THEN D.Col1 ELSE P.Col1 END AS NewCol1
	   ,CASE WHEN P.Col2 IS NULL THEN D.Col2 ELSE P.Col2 END AS NewCol2
	   ,CASE WHEN P.Col3 IS NULL THEN D.Col3 ELSE P.Col3 END AS NewCol3
	   ,CASE WHEN P.Col4 IS NULL THEN D.Col4 ELSE P.Col4 END AS NewCol4
	   ,CASE WHEN P.Col5 IS NULL THEN D.Col5 ELSE P.Col5 END AS NewCol5
 FROM [dbo].[Projection] P JOIN Deleted D ON P.ID=D.ID 
 WHERE P.Col1 IS NULL OR P.Col2 IS NULL OR P.Col3 IS NULL OR P.Col4 IS NULL OR P.Col5 IS NULL
)
UPDATE CTE 
SET Col1=NewCol1,Col2=NewCol2,Col3=NewCol3,Col4=NewCol4,Col5=NewCol5

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.