Please check the SQL snippet below and also the attached image for before and after snapshots of the data. I hope it helps.

-- Table1 has just MyID...rest of the columns all have NULL values

DECLARE @Table1 TABLE (MyID INT, ID1 INT, ID2 INT, ID3 INT, ID4 INT)

INSERT @Table1 (MyID, ID1, ID2, ID3, ID4) VALUES (1, NULL, NULL, NULL, NULL), (2, NULL, NULL, NULL, NULL), (3, NULL, NULL, NULL, NULL), (4, NULL, NULL, NULL, NULL)

SELECT * FROM @Table1

-- Table2 has multiple values for MyID; each MyID has multiple ColumnNames (a max of 4, from ID1 thru ID4) and corresponding ColumnValues

DECLARE @Table2 TABLE (MyID INT, ColumnName VARCHAR (10), ColumnValue INT)

INSERT @Table2 VALUES (1, 'ID1', 10), (1, 'ID2', 20), (1, 'ID3', 30), (1, 'ID4', 40), (2, 'ID1', 101), (2, 'ID2', 102), (2, 'ID3', 103), (3, 'ID1', 1001), (3, 'ID3', 1003), (4, 'ID1', 111), (4, 'ID2', 222), (4, 'ID3', 333), (4, 'ID4', 444)

SELECT * FROM @Table2

-- Update Table1 by PIVOTing Table2 to match the structure

UPDATE@Table1

SETt1.ID1 = pvt.ID1,

t1.ID2 = pvt.ID2,

t1.ID3 = pvt.ID3,

t1.ID4 = pvt.ID4

FROM@Table1 t1

INNER JOIN (

SELECT*

FROM@Table2

PIVOT

(AVG(ColumnValue) FOR ColumnName IN ([ID1], [ID2], [ID3], [ID4])

) AS MyVal

) pvt

ON t1.MyID = pvt.MyID

SELECT * FROM @Table1

- RexHelios