sql-server - 当 N 在 from 中时如何更新 N 行(从 @myVar 中选择 N)

Question

我正在 SQL Server 2012 上开发这个存储过程。

存储过程将为 @newBatches 参数中的每一行更新 EXTERNAL_CODES 表中的 Quantity 行。这就像一个循环，我需要在 BATCHES 表中为 @newBatches 参数中的每一行创建一个新行。

然后，我必须使用创建的每个 batchId 更新 EXTERNAL_CODES 表中的 Quantity 行。

CREATE PROCEDURE [dbo].[CreateBatchAndKeepExternalCodes]
      @newBatches as dbo.CreateBatchList READONLY,
      @productId int
AS
    set nocount on;

    declare @lowestCodeLevel tinyint;

-- ======== VALIDATION ==========
    if ((select count(name) from @newBatches) = 0)
        return -112;

-- ====== CODE ========

    -- Get lowest aggregation level.
    set @lowestCodeLevel = 
        (select min(c.application_code)
            from CHINA_CODES_HEADER c, PRODUCTS p
            where p.Id = @productId and c.DRUG_TEN_SEATS = p.PRODUCT_CODE);

    begin transaction;

        insert into BATCHES (PRODUCT_ID, NAME, CREATED)
            select @productId, Name, CAST(SYSDATETIMEOFFSET() as nvarchar(50))
              from @newBatches;

        update top(t.Quantity) EXTERNAL_CODES 
           set BATCH_ID = (select ID from BATCHES where NAME = t.Name)
             , USED = 1
         from (select Name, Quantity from @newBatches) t
         where PRODUCT_ID = @productId and CODE_LEVEL = @lowestCodeLevel;

    commit transaction;

RETURN 0

我在这个更新上遇到错误:

update top(t.Quantity) EXTERNAL_CODES 
  set BATCH_ID = (select ID from BATCHES where NAME = t.Name)
    , USED = 1
from (select Name, Quantity from @newBatches) t
where PRODUCT_ID = @productId and CODE_LEVEL = @lowestCodeLevel;

错误在这里:update top(t.Quantity)。它找不到 t.Quantity。

dbo.CreateBatchList 是:

CREATE TYPE [dbo].[CreateBatchList] AS TABLE
(
    Name nVARCHAR(20),
    Quantity int
)

我的问题是我无法设置更新 Quantity 行。任何的想法？

错误(或警告)信息是:

SQL71005: The reference to the column t.Quantity could not be resolved.

也许我可以使用 MERGE。

Answer 1

你的更新声明很困惑。如果例如 @newBatches表有多行，那么你是说，选择所有 Quantity来自 @newBatches在Top ？

无论如何，我认为解决方案是使用循环来使用 @newBatches 中的每一行更新。我修改了您的代码以便在我这边进行测试，并用表变量替换了所有表。您可能会发现它很有帮助。

但仍然没有任何Order By子句并且在不了解实际业务逻辑的情况下，我不能说这个解决方案是正确的。

DECLARE @productID int;
DECLARE @lowestCodeLevel int;

DECLARE @EXTERNAL_CODES table(BATCH_ID varchar(100), USED bit, PRODUCT_ID int, CODE_LEVEL int);
DECLARE @BATCHES table(ID int, NAME varchar(100));
DECLARE @newBatches table(Name nVARCHAR(20), Quantity int);



-- we don't know at this point whether @newBatches has some column
-- through which we can uniquely identify a row
-- that is why we are creating this new table in which we have Row_ID column
-- through which we can extract each line
DECLARE @newBatchesWithRowID table(Row_ID int not null identity, Name nVarchar(20), Quantity int);

INSERT INTO @newBatchesWithRowID(Name, Quantity)
    SELECT  Name, Quantity
    FROM    @newBatches;

DECLARE @prvRow_ID int;

-- loop to iterate in @newBatchesWithRowID table
WHILE(1 = 1)
Begin
    DECLARE @row_ID int = NULL;
    DECLARE @Name varchar(100);
    DECLARE @Quantity int;

    SELECT  TOP 1 @row_ID = Row_ID
            , @Quantity = Quantity
            , @Name = Name
    FROM    @newBatchesWithRowID
    WHERE   Row_ID > @prvRow_ID OR @prvRow_ID IS NULL
    ORDER BY Row_ID;
    If @row_ID IS NULL Break;
    SET @prvRow_ID = @row_ID;

    update top(@Quantity) @EXTERNAL_CODES 
        set BATCH_ID = (select ID from @BATCHES where NAME = @Name)
        , USED = 1
    where PRODUCT_ID = @productId and CODE_LEVEL = @lowestCodeLevel;
END