How to update N rows when N is in (select N from @myVar)
I am developing this Stored Procedure on SQL Server 2012.
The stored procedure will update the rows Quantity
in the table EXTERNAL_CODES
for each row in the parameter @newBatches
. It's like a loop, I will need to create a new row in the table BATCHES
for every row in the parameter @newBatches
.
And then I need to update the rows Quantity
in the table EXTERNAL_CODES
with each one created 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
I am getting the error 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;
Error here: update top(t.Quantity)
. He cannot find t.Quantity
.
dbo.CreateBatchList
:
CREATE TYPE [dbo].[CreateBatchList] AS TABLE
(
Name nVARCHAR(20),
Quantity int
)
My problem is that I cannot install row update Quantity
. Any ideas?
Error (or warning) message:
SQL71005: The t.Quantity column reference could not be resolved.
Perhaps I could use MERGE
.
source to share
Your update statement is pretty confusing. If, for example, a table @newBatches
has multiple rows, then you say select all Quantity
from @newBatches
to Top
?
Anyway, I think the solution is to use a loop to use each row from @newBatches
to update. I modified your code to test it on my side and replaced all tables with table variables. You may find this helpful.
But still, without a suggestion Order By
and without knowing the actual business logic, I cannot say that this solution is correct.
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
source to share