如何更新sql中列值中存在行值的價格？

我想更新列 p1 但 p1 相同的產品。

更新列 p1，其中 C1 中的範例 g6-1se 存在於列名中，並通過 g6-1sr 行中的值 p1 更新 g6-1se 行中 p1 列中的 null 值。

在第 1 行中，讀取 g6-1 並在列名中搜尋並更新列 p1（如果存在）。讀完 g6-1sr 並蒐索直到到達第 1 行 c1 列的終點線，然後指針，指向更新前的行不是空列 p1。

更新前的tbl：

   +-------+-------------+-----------------------------------------------+-------+
   | id    | Name        | C1                                            | P1    |
   +-------+-------------+-----------------------------------------------+-------+
   | 29214 | g6-1sr      | g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1         | 28000 |
   | 29215 | g6-1se      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29226 | g6-1sf      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29237 | g6-1se1     | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29248 | g6-1se12    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29259 | Nkg6-1se    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29269 | N56-1341se  | N56-11, N56-1341se, N56-1100, N56-1348se,     | 32000 |
   | 29270 | N56-1348se  | N56-11, N56-21, N56-1100, N56-2980,           | null  |
   | 29271 | F566 1341se | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 38000 |
   +-------+-------------+-----------------------------------------------+-------+

更新後的tbl：

   +-------+-------------+-----------------------------------------------+-------+
   | id    | Name        | C1                                            | P1    |
   +-------+-------------+-----------------------------------------------+-------+
   | 29214 | g6-1sr      | g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1         | 28000 |
   | 29215 | g6-1se      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
   | 29226 | g6-1sf      | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29237 | g6-1se1     | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
   | 29248 | g6-1se12    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 28000 |
   | 29259 | Nkg6-1se    | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | null  |
   | 29269 | N56-1341se  | N56-11, N56-1341se, N56-1100, N56-1348se,     | 32000 |
   | 29270 | Asus N56-1348se  | N56-11, N56-21, N56-1100, N56-2980, N56-2| 32000 |
   | 29271 | F566 1341se | g6-1, g6-2, g6-1000, g6-1980,       g6-1230,  | 38000 |
   +-------+-------------+-----------------------------------------------+-------+

嘗試使用游標但不成功

檢查它：這裡的 dbfiddle

基本上，獲取 P1 為空的所有行，並將其與所有具有 P1 值且名稱如 c1 的行連接起來。

使用喜歡

UPDATE     t1 
SET        P1 = t2.P1
FROM       tbl1 t1
INNER JOIN tbl1 t2 
ON         t2.P1 IS NOT NULL 
AND        t2.c1 LIKE '%' + t1.Name + '%'
WHERE      t1.P1 IS NULL;

|   id|Name       |C1                                          |   P1|
|----:|:----------|:-------------------------------------------|----:|
|29214|g6-1sr     |g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1       |28000|
|29215|g6-1se     |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|28000|
|29226|g6-1sf     |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|     |
|29237|g6-1se1    |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|28000|
|29248|g6-1se12   |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|28000|
|29259|Nkg6-1se   |g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|     |
|29269|N56-1341se |N56-11, N56-1341se, N56-1100, N56-1348se,   |32000|
|29270|N56-1348se |N56-11, N56-21, N56-1100, N56-2980,         |32000|
|29271|F566 1341se|g6-1, g6-2, g6-1000, g6-1980,       g6-1230,|38000|

使用 dbo.SplitCompare() 函式

UPDATE     t1 
SET        P1 = t2.P1
FROM       tbl1 t1
INNER JOIN tbl1 t2 
ON         t2.P1 IS NOT NULL 
AND        dbo.SplitCompare(t2.C1, t1.Name) = 1
WHERE      t1.P1 IS NULL;

更新

檢查它：這裡的 dbfiddle

首先，我添加了一個新功能：$$ dbo $$.$$ SplitCompare $$

-------------------------------------------------------------------------------------
-- Compares each item of a coma-delimited string (without trainling spaces), 
-- against @ToSearch.
--
-- Returns: (int)
--
--    1 - @ToSearch matches some item
--    0 - No matches found.
-------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[SplitCompare] (@List nvarchar(MAX), @ToSearch nvarchar(1024))
RETURNS int
AS
BEGIN
   DECLARE @Item nvarchar(1024) = null;
   DECLARE @Result int = 0;

   WHILE LEN(@List) > 0
   BEGIN
       IF PATINDEX('%,%', @List) > 0
       BEGIN
           SET @Item = SUBSTRING(@List, 0, PATINDEX('%,%', @List));
           SET @List = SUBSTRING(@List, LEN(@Item + ';') + 1, LEN(@List));
       END
       ELSE
       BEGIN
           SET @Item = @List;
           SET @List = NULL;
        END

   IF RTRIM(LTRIM(@Item)) = @ToSearch -- does it match?
   BEGIN
       SET @Result = 1;
       SET @List = '';
   END
   END

   RETURN @Result;

END

其次，根據 OP 要求，我使用 CURSOR 實現了這個解決方案。

-------------------------------------------------------------------------------------
-- Use @LimitRows just to limit the number of rows to be updated each time 
-- the script is executed.
-------------------------------------------------------------------------------------
DECLARE @LimitRows int = 100;
DECLARE @Id int, @Name nvarchar(2048), @P1 int;

-- only selects rows where P1 IS NULL
DECLARE CurPrices CURSOR
   FOR SELECT TOP (@LimitRows) Id, Name FROM #tbl1 WHERE P1 IS NULL;

OPEN CurPrices 
FETCH NEXT FROM CurPrices INTO @Id, @Name

WHILE @@FETCH_STATUS = 0  
BEGIN

   SET @P1 = NULL;

   -- note: top (1)
   --
   SELECT   TOP (1) @P1 = t.P1
   FROM     tbl1 t
   WHERE    t.P1 IS NOT NULL
   AND      dbo.SplitCompare(t.C1, @Name) = 1
   ORDER BY Id;

   IF COALESCE(@P1,0) > 0
   BEGIN
       UPDATE #tbl1
       SET    P1 = @P1
       WHERE  Id = @Id;

       IF @@ERROR <> 0
       BEGIN
          CLOSE CurPrices;
          DEALLOCATE CurPrices;
          RAISERROR('ERROR', 20, -1);
       END
   END

   FETCH NEXT FROM CurPrices INTO @Id, @Name    
END

CLOSE CurPrices;
DEALLOCATE CurPrices;

結果與第一個命令相同，但此命令允許在每次執行時限制受影響的行數。

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