当 N 位于 from 中时如何更新 N 行（从 @myVar 选择 N）

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

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

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

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:

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 is:

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

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

错误（或警告）消息是：

SQL71005：无法解析对列 t.Quantity 的引用。

也许我可以用MERGE.

你的更新声明很混乱。如果举例来说@newBatches表有多行，那么你是说，选择所有Quantity from @newBatches in 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