Update statement using Case statement, derived table, and ROW_NUMBER Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their corresponding columns in a table. I'm using a combination of CASE statements, a derived table, along with ROW_NUMBER in order to assign the correct product-ID value to each corresponding column. For some reason, it only seems to update the product_id_1 column, which is not right since there are other values stored in the #contactProducts table Below please find the T-SQL to recreate my issue:

CREATE TABLE #contactProducts(
[contact_product_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[product_id] [varchar](50) NOT NULL,
[createDt] [datetime] NOT NULL,
CONSTRAINT [PK_#contactProducts] PRIMARY KEY CLUSTERED 
(
[contact_product_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #contactProducts VALUES(3,'B6281-81',getDate())
INSERT INTO #contactProducts VALUES(3,'B100839-1',getDate())
INSERT INTO #contactProducts VALUES(3,'B101203-5',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())

SELECT * FROM #contactProducts

-- Temp_Import_2
CREATE TABLE #temp_import_2(
[temp_import_2_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[PRODUCT_IDS] [varchar](2000) NULL,
[PRODUCT_ID_1] [varchar](50) NULL,
[PRODUCT_ID_2] [varchar](50) NULL,
[PRODUCT_ID_3] [varchar](50) NULL,
[PRODUCT_ID_4] [varchar](50) NULL,
[PRODUCT_ID_5] [varchar](50) NULL,
[PRODUCT_ID_6] [varchar](50) NULL,
[PRODUCT_ID_7] [varchar](50) NULL,
[PRODUCT_ID_8] [varchar](50) NULL,
[PRODUCT_ID_9] [varchar](50) NULL,
[PRODUCT_ID_10] [varchar](50) NULL,
CONSTRAINT [temp_import_2] PRIMARY KEY CLUSTERED 
(
[temp_import_2_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #temp_import_2 (temp_import_id, product_IDs)
VALUES(3, 'B6281-81,B100839-1,B101203-5,B101204-7,B102472-14')

SELECT * FROM [#temp_import_2]

-- update with derived table
-- even tho there are values for thisRowNum = 2,3,4, etc. they don't seem to update. only works for thisRowNum = 1

UPDATE #temp_import_2 SET
 #temp_import_2.PRODUCT_ID_1 = CASE WHEN derivedTbl.thisRowNum = 1 THEN derivedTbl.product_id END
,#temp_import_2.PRODUCT_ID_2 = CASE WHEN derivedTbl.thisRowNum = 2 THEN derivedTbl.product_id ELSE '2' END
,#temp_import_2.PRODUCT_ID_3 = CASE WHEN derivedTbl.thisRowNum = 3 THEN derivedTbl.product_id ELSE '3' END
,#temp_import_2.PRODUCT_ID_4 = CASE WHEN derivedTbl.thisRowNum = 4 THEN derivedTbl.product_id ELSE '4' END
,#temp_import_2.PRODUCT_ID_5 = CASE WHEN derivedTbl.thisRowNum = 5 THEN derivedTbl.product_id ELSE '5' END
,#temp_import_2.PRODUCT_ID_6 = CASE WHEN derivedTbl.thisRowNum = 6 THEN derivedTbl.product_id ELSE '6' END
,#temp_import_2.PRODUCT_ID_7 = CASE WHEN derivedTbl.thisRowNum = 7 THEN derivedTbl.product_id ELSE '7' END
,#temp_import_2.PRODUCT_ID_8 = CASE WHEN derivedTbl.thisRowNum = 8 THEN derivedTbl.product_id ELSE '8' END
,#temp_import_2.PRODUCT_ID_9 = CASE WHEN derivedTbl.thisRowNum = 9 THEN derivedTbl.product_id ELSE '9' END
,#temp_import_2.PRODUCT_ID_10 = CASE WHEN derivedTbl.thisRowNum = 10 THEN derivedTbl.product_id ELSE '10' END
FROM (
select temp_import_id, product_id, ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
from #contactProducts
) derivedTbl INNER JOIN #temp_import_2 ON derivedTbl.temp_import_id = #temp_import_2.temp_import_id

SELECT * FROM [#temp_import_2]

DROP TABLE #contactProducts

DROP TABLE [#temp_import_2]
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