Update statement using Case statement, derived table, and ROW_NUMBER

Question

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]

McNets · Accepted Answer · 2017-04-20 22:35:22Z

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

Nic · Accepted Answer · 2017-04-20 22:24:50Z

You need to perform additional logic on the #temp_import_2 table to make that a single row, eliminating the multiple rows, which will not update as you expect them to. Effectively you are pivoting the data here. A pivot command, or multiple temp tables may be more efficient at getting you the results that you desire, but the following is a functional start where I've used command table expressions to do the work of pivoting the data.

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]

;
WITH    cteAllProducts
          AS ( SELECT   temp_import_id ,
                        product_id ,
                        ROW_NUMBER() OVER ( PARTITION BY temp_import_id ORDER BY contact_product_id ) AS thisRowNum
               FROM     #contactProducts
             ),
        cteConvergedProducts
          AS ( SELECT   d.temp_import_id ,
                        PRODUCT_ID_1 = CASE WHEN d.thisRowNum = 1
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_2 = CASE WHEN d.thisRowNum = 2
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_3 = CASE WHEN d.thisRowNum = 3
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_4 = CASE WHEN d.thisRowNum = 4
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_5 = CASE WHEN d.thisRowNum = 5
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_6 = CASE WHEN d.thisRowNum = 6
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_7 = CASE WHEN d.thisRowNum = 7
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_8 = CASE WHEN d.thisRowNum = 8
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_9 = CASE WHEN d.thisRowNum = 9
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_10 = CASE WHEN d.thisRowNum = 10
                                             THEN d.product_id
                                        END
               FROM     cteAllProducts d
                        INNER JOIN #temp_import_2 t ON d.temp_import_id = t.temp_import_id
             ),
        cteGroupedResults
          AS ( SELECT   c.temp_import_id ,
                        PRODUCT_ID_1 = COALESCE(MAX(c.PRODUCT_ID_1), '1') ,
                        PRODUCT_ID_2 = COALESCE(MAX(c.PRODUCT_ID_2), '2') ,
                        PRODUCT_ID_3 = COALESCE(MAX(c.PRODUCT_ID_3), '3') ,
                        PRODUCT_ID_4 = COALESCE(MAX(c.PRODUCT_ID_4), '4') ,
                        PRODUCT_ID_5 = COALESCE(MAX(c.PRODUCT_ID_5), '5') ,
                        PRODUCT_ID_6 = COALESCE(MAX(c.PRODUCT_ID_6), '6') ,
                        PRODUCT_ID_7 = COALESCE(MAX(c.PRODUCT_ID_7), '7') ,
                        PRODUCT_ID_8 = COALESCE(MAX(c.PRODUCT_ID_8), '8') ,
                        PRODUCT_ID_9 = COALESCE(MAX(c.PRODUCT_ID_9), '9') ,
                        PRODUCT_ID_10 = COALESCE(MAX(c.PRODUCT_ID_10), '10')
               FROM     cteConvergedProducts c
               GROUP BY c.temp_import_id
             )
    UPDATE  #temp_import_2
    SET     PRODUCT_ID_1 = c.PRODUCT_ID_1 ,
            PRODUCT_ID_2 = c.PRODUCT_ID_2 ,
            PRODUCT_ID_3 = c.PRODUCT_ID_3 ,
            PRODUCT_ID_4 = c.PRODUCT_ID_4 ,
            PRODUCT_ID_5 = c.PRODUCT_ID_5 ,
            PRODUCT_ID_6 = c.PRODUCT_ID_6 ,
            PRODUCT_ID_7 = c.PRODUCT_ID_7 ,
            PRODUCT_ID_8 = c.PRODUCT_ID_8 ,
            PRODUCT_ID_9 = c.PRODUCT_ID_9 ,
            PRODUCT_ID_10 = c.PRODUCT_ID_10
    FROM    #temp_import_2 t
            JOIN cteGroupedResults c ON c.temp_import_id = t.temp_import_id;

SELECT  *
FROM    [#temp_import_2];

DROP TABLE #contactProducts;

DROP TABLE [#temp_import_2];

Stack Exchange Network

Update statement using Case statement, derived table, and ROW_NUMBER

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server-2008-r2
update
derived-tables
or ask your own question.

Hot Network Questions

Update statement using Case statement, derived table, and ROW_NUMBER

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server-2008-r2updatederived-tables or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server-2008-r2
update
derived-tables
or ask your own question.