Using TOP(variable) with a subset of data

Question

I have abstracted the problem to the following situation:

I have a table (A) that contains the number (Quantity) of items I want to update. Next I have a table (SL) that contains the references to table (A) that I need to select from. And finally the table that needs to get updated (B)

CREATE TABLE A
(
    Id int,
    Quantity int
)

CREATE TABLE SL
(
    Id int,
    A_Id int,
    S_Id int
)

CREATE TABLE B
(
    Id int,
    StatusValue int,
    A_Id int, 
    S_Id int NULL,
)

So let's insert some data for testing purposes:

INSERT INTO A Values (1, 4), (2, 2), (3, 3), (4, 4), (5, 2)

delete from B
declare @i int = 1;
declare @j int = 0;
declare @maxA int = 5;
declare @rows_to_insert int = 10;
while @i < @maxA
begin
    while @j < @rows_to_insert * @i
    begin
        INSERT INTO B VALUES (10+@j, 0, @i, null)
        set @j = @j + 1
    end
    set @i = @i + 1
end
select * from B

INSERT INTO SL Values (1, 1, 1), (2, 2, 1), (3 ,2, 1)

And now on to the problem. I want to update TOP(Quantity) of records in B relating to the records in SL. Basically this is what I want to do, but it is unsupported in SQL:

DECLARE @Sale_Id int = 1;
WITH AB (AId, AQuantity, SaleId)
AS
(
    SELECT A.Id, A.Quantity, SL.S_Id FROM A
        INNER JOIN SL on A.Id = SL.A_Id
        WHERE SL.S_Id = @Sale_Id
)
UPDATE TOP(AB.Quantity) B
    SET StatusValue = 1,
        S_Id = AB.SaleId
    FROM AB
    WHERE StatusValue = 0 -- Edited
                AND B.A_Id = AB.AId

The error message is

Msg 4104, Level 16, State 1, Line 55 The multi-part identifier "AB.Quantity" could not be bound.

what are my options of getting this done? (There is always the Cursor but is that a good option?)

Note: The data has a funny side to it that in SL there is 2 times a record referencing A_Id = 2. This implies that the result needs to have 4 B records with A_Id = 2 updated.

RichardTheKiwi · Answer 1 · 2012-11-29T11:51:20.590

1

This should work

DECLARE @Sale_Id int = 1;
with tmp as (
  SELECT A.Id, A.Quantity, SL.S_Id SaleID, B.S_ID, b.id b_id, B.StatusValue,
         rn=dense_rank() over (partition by a.id order by b.id)
    FROM A
    JOIN SL on A.Id = SL.A_Id
    JOIN B ON B.A_Id = A.Id
   WHERE SL.S_Id = @Sale_Id
)
update tmp
   set S_ID = SaleID,
       StatusValue = 1
 where rn <= quantity;

However, your data looks funny with A.id=2 being sold twice on the same SL.id=1.

SQL Fiddle

edited Nov 29 '12 at 11:51

answered Nov 29 '12 at 11:45

RichardTheKiwi

105,798
26
196
262

Yes, the data looks funny that way, having A.Id being listed twice, its the business rules :) Looking at the result from the SQL Fiddle, it does not Update B with 4 time A.id = 2; – Rudi Nov 29 '12 at 11:59

score 0 · Accepted Answer · answered Dec 05 '12 at 10:49

So it seems the Cursor is the way I'll need to go:

DECLARE @A_Id int, @Quantity int;
DECLARE ABCursor CURSOR LOCAL READ_ONLY FOR 
    SELECT A.Id, A.Quantity FROM A
        INNER JOIN SL on A.Id = SL.A_Id
        WHERE SL.S_Id = @Sale_Id
OPEN ABCursor
FETCH NEXT FROM ABCursor 
    INTO @A_Id, @Quantity
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE TOP(@Quantity) B
        SET StatusValue = 1,
            S_Id = @Sale_Id
        WHERE StatusValue = 0
            AND B.A_Id = @A_Id
            AND S_Id is null
    FETCH NEXT FROM ABCursor 
        INTO @A_Id, @Quantity
END 
CLOSE ABCursor;
DEALLOCATE ABCursor;

Now must read up on what is the best definition for the Cursor i.e.: LOCAL READ_ONLY

or why a cursor might not be as bad as 'generally accepted' : http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much — Rudi, Dec 05 '12 at 12:35
`LOCAL FAST_FORWARD` would be a better intentional statement. — Rudi, Dec 05 '12 at 12:51

Using TOP(variable) with a subset of data

2 Answers2

SQL Fiddle