Update statement using Case statement, derived table, and ROW_NUMBER
You cannot get it on this way.

First, have a look at next query:




SELECT d.*, ti2.*
FROM #temp_import_2 ti2
INNER JOIN (select temp_import_id, product_id, 
                   ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
            from #contactProducts) d 
ON    d.temp_import_id = ti2.temp_import_id;
GO



temp_import_id | product_id | thisRowNum | temp_import_2_id | temp_import_id | PRODUCT_IDS                                       | PRODUCT_ID_1 | PRODUCT_ID_2 | PRODUCT_ID_3 | PRODUCT_ID_4 | PRODUCT_ID_5 | PRODUCT_ID_6 | PRODUCT_ID_7 | PRODUCT_ID_8 | PRODUCT_ID_9 | PRODUCT_ID_10
-------------: | :--------- | :--------- | ---------------: | -------------: | :------------------------------------------------ | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :------------
             3 | B6281-81   | 1          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B100839-1  | 2          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101203-5  | 3          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 4          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 5          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         



As you can see, it returns 5 rows. And you cannot update one record from 5 rows using a CASE statement. 

May be there is a easiest solution, but you must add a single SELECT for each PRODUCT_X




SELECT d.*, ti2.*
FROM #temp_import_2 ti2
INNER JOIN (select temp_import_id, product_id, 
                   ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
            from #contactProducts) d 
ON    d.temp_import_id = ti2.temp_import_id;
GO



temp_import_id | product_id | thisRowNum | temp_import_2_id | temp_import_id | PRODUCT_IDS                                       | PRODUCT_ID_1 | PRODUCT_ID_2 | PRODUCT_ID_3 | PRODUCT_ID_4 | PRODUCT_ID_5 | PRODUCT_ID_6 | PRODUCT_ID_7 | PRODUCT_ID_8 | PRODUCT_ID_9 | PRODUCT_ID_10
-------------: | :--------- | :--------- | ---------------: | -------------: | :------------------------------------------------ | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :------------
             3 | B6281-81   | 1          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B100839-1  | 2          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101203-5  | 3          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 4          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 5          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         






with rnum as 
(
    select temp_import_id, product_id, 
           ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
    from #contactProducts
)
UPDATE #temp_import_2
SET    PRODUCT_ID_1 = (SELECT COALESCE(product_id, '1') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 1),
       PRODUCT_ID_2 = (SELECT COALESCE(product_id, '2') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 2),
       PRODUCT_ID_3 = (SELECT COALESCE(product_id, '3') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 3),
       PRODUCT_ID_4 = (SELECT COALESCE(product_id, '4') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 4),
       PRODUCT_ID_5 = (SELECT COALESCE(product_id, '5') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 5),
       PRODUCT_ID_6 = (SELECT COALESCE(product_id, '6') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 6),
       PRODUCT_ID_7 = (SELECT COALESCE(product_id, '7') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 7),
       PRODUCT_ID_8 = (SELECT COALESCE(product_id, '8') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 8),
       PRODUCT_ID_9 = (SELECT COALESCE(product_id, '9') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 9),
       PRODUCT_ID_10 = (SELECT COALESCE(product_id, '10') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 10)
FROM   #temp_import_2 ti2
;

select * from #temp_import_2;
GO



temp_import_2_id | temp_import_id | PRODUCT_IDS                                       | PRODUCT_ID_1 | PRODUCT_ID_2 | PRODUCT_ID_3 | PRODUCT_ID_4 | PRODUCT_ID_5 | PRODUCT_ID_6 | PRODUCT_ID_7 | PRODUCT_ID_8 | PRODUCT_ID_9 | PRODUCT_ID_10
---------------: | -------------: | :------------------------------------------------ | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :------------
               1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | B6281-81     | B100839-1    | B101203-5    | B101204-7    | B101204-7    | null         | null         | null         | null         | null         



dbfiddle here