tsql - 根据通用名称填充列行（子查询仅返回一个值）

Question

我正在尝试使用与 TempName 列中的相同名称匹配的 SN 填充 CABSN 列中的空白

TempName    CabSN   SN      Name     Order  RowID
DevCab01    SN12345 SN12345 DevCab01  19    1
DevCab01            SN12346 Test2     18    2
DevCab01            SN12347 Test3     17    3
DevCab01            SN12348 Test4     16    4
DevCab01            SN12352 Test8     15    5
DevCab01            SN12353 Test9     14    6
DevCab01            SN12354 Test10    13    7
DevCab02   SN12355  SN12355 DevCab02   9    8
DevCab02            SN12356 Test12     8    9
DevCab02            SN12357 Test13     7    10
DevCab02            SN12358 Test14     6    11
DevCab03   SN12359  SN12359 DevCab03   5    12
DevCab03            SN12360 Test16     4    13
DevCab03            SN12361 Test17     3    14
DevCab04   SN12349  SN12349 DevCab04  15    15
DevCab04            SN12350 Test6     14    16
DevCab04            SN12351 Test7     13    17

我的脚本尝试（失败）用匹配的 TempName 填充 CabSN 中的空白行

DECLARE @CabID AS nvarchar(50)
SET @CabID = NULL

（无论我将变量放在哪里，它都不起作用，显示多个返回值）

UPDATE m
set 
m.[CabSN] = 
CASE WHEN m.[CabSN] is NULL 
    THEN (
        SELECT m3.[CabSN] 
        FROM [tblname1] m3 
        JOIN inserted i ON i.[TempName] = m3.[TempName] 
        WHERE m3.[RowID] = 
        (
        SELECT MAX(i.RowID)
        FROM [tblname1] m2
        JOIN inserted i ON i.[TempName] = m2.[TempName]
        WHERE m2.[RowID] < m.[RowID] 
        and m2.[CabSN] is not NULL)
        )
    ELSE m.[CabSN]

标签： tsqlsql-updatesubquery

Answer 1

完整的工作示例：

DECLARE @DataSource TABLE
(
    [TempName] VARCHAR(12)
   ,[CabSN] VARCHAR(12)
   ,[SN] VARCHAR(12)
   ,[Name] VARCHAR(12)
   ,[Order] SMALLINT
   ,[RowID] SMALLINT
);

INSERT INTO @DataSource ([TempName], [CabSN], [SN], [Name], [Order], [RowID])
VALUES ('DevCab01', 'SN12345', 'SN12345', 'DevCab01', '19', '1')
    ,('DevCab01', '', 'SN12346', 'Test2', ' 18', '2')
    ,('DevCab01', '', 'SN12347', 'Test3', ' 17', '3')
    ,('DevCab01', '', 'SN12348', 'Test4', ' 16', '4')
    ,('DevCab01', '', 'SN12352', 'Test8', ' 15', '5')
    ,('DevCab01', '', 'SN12353', 'Test9', ' 14', '6')
    ,('DevCab01', '', 'SN12354', 'Test10', '13', '7')
    ,('DevCab02', 'SN12355', 'SN12355', 'DevCab02', '9', '8')
    ,('DevCab02', '', 'SN12356', 'Test12', ' 8', '9')
    ,('DevCab02', '', 'SN12357', 'Test13', ' 7', '10')
    ,('DevCab02', '', 'SN12358', 'Test14', ' 6', '11')
    ,('DevCab03',  'SN12359', 'SN12359', 'DevCab03', '5', '12')
    ,('DevCab03', '', 'SN12360', 'Test16', ' 4', '13')
    ,('DevCab03', '', 'SN12361', 'Test17', ' 3', '14')
    ,('DevCab04',  'SN12349', 'SN12349', 'DevCab04', '15', '15')
    ,('DevCab04', '', 'SN12350', 'Test6', ' 14', '16')
    ,('DevCab04', '', 'SN12351', 'Test7', ' 13', '17');

WITH DataSource AS
(
    SELECT DISTINCT [TempName]
                   ,[CabSN]
    FROM @DataSource
    WHERE  [CabSN] <> '' 
)
UPDATE @DataSource
SET [CabSN] = S.[CabSN]
FROM @DataSource T
INNER JOIN DataSource S
    ON T.[TempName] = S.[TempName]
WHERE T.[CabSN] = '';

SELECT *
FROM @DataSource;