Sql-Server-2008-R2

使用 Case 語句、派生表和 ROW_NUMBER 的更新語句

  • April 20, 2017

執行 MS SQL 2008 R2。我提供了一個產品 ID 的 CSV 列表，我將其解析為各個行並進行規範化。然後，我需要獲取這些產品 ID（從 1 到 10 的任意位置）並將它們寫入表中相應的列。我正在使用 CASE 語句、派生表以及 ROW_NUMBER 的組合，以便為每個相應的列分配正確的產品 ID 值。由於某種原因，它似乎只更新了 product_id_1 列，這是不正確的，因為#contactProducts 表中儲存了其他值，請在下面找到 T-SQL 來重新創建我的問題：

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]

你不能通過這種方式得到它。

首先，看看下一個查詢：

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 | 產品編號 | 這個行號 | 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 | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* 
 3 | B100839-1 | 2 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* |*空* | *空* | *空* | *空* | *空* | *空* 
 3 | B101203-5 | 3 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空值* 
 3 | B101204-7 | 4 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* 
 3 | B101204-7 | 5 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* |*空* | *空* | *空* | *空* | *空* | *零*

如您所見，它返回 5 行。而且您不能使用 CASE 語句從 5 行中更新一條記錄。

可能有一個最簡單的解決方案，但您必須為每個 PRODUCT_X 添加一個 SELECT

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 | 產品編號 | 這個行號 | 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 | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* 
 3 | B100839-1 | 2 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* |*空* | *空* | *空* | *空* | *空* | *空* 
 3 | B101203-5 | 3 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空值* 
 3 | B101204-7 | 4 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* | *空* 
 3 | B101204-7 | 5 | 1 | 3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | *空* | *空* | *空* | *空* |*空* | *空* | *空* | *空* | *空* | *零*         

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 | *空* | *空* | *空* | *空* | *空值*

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/171568

相關問答

Sql-Server-2008-R2

如何修復此 SQL 伺服器查詢 2008r2

  • January 30, 2022
檢視問答
Sql-Server

在合併更新語句中使用聚合

  • May 7, 2020
檢視問答
Mysql

如果在更新的派生表中，則忽略 Order by（僅在 MYSQL 5.7 中）

  • September 4, 2019
檢視問答
Sql-Server

幫助使用 JOIN 編寫 UPDATE 語句

  • May 1, 2018
檢視問答
Sql-Server

SQL 更新目標表中具有相同 id 但與源表不同值的多行

  • April 3, 2018
檢視問答
Sql-Server

更新列的順序？

  • January 8, 2018
檢視問答