Is there a way to UPDATE TOP (N) with inner join where N is a field of such inner join?

Question

I'm trying to create a script that synchronizes Sales and Inventory tables. For that I wrote an UPDATE on the Inventory table (which has 1 record per item of inventory present) like this:

UPDATE TOP (q.QuantitySold) i
SET i.Converted = 1,
    i.CartID = q.CartID,
    i.ReservedDate = GETDATE()
FROM Inventory i
INNER JOIN
(
    SELECT product.ProductID, sales.CartID, COUNT(sales.ID) AS QuantitySold
    FROM Products product
    INNER JOIN Sales sales ON sales.ProductID = product.ProductID
    WHERE <conditions> 
    GROUP BY product.ProductID, sales.CartID
) q ON q.ProductID = i.ProductID
WHERE i.Converted = 0 AND i.CartID IS NULL

But it's not working, error says q.QuantitySold couldn't be bound.

Is there a way to update N records of inventory (equal to the quantity sold) without using a cursor? I refuse to give up like that.

Note: this is a simplified version of the actual query.

You have top with no order by, so even if this did work you have no way of knowing which rows would get updated. But yes you can do this, just not like you are trying to do. You don't update the top x rows, you use your query to return only the top 10 rows. The simplest method would be to use a cte to get the top 10 and then update the cte. — Sean Lange, Feb 08 '19 at 20:07
I know, but in this particular case I don't care about which rows get updated as long as they are not converted and with no cart id. Of course it's not very neat to have a non deterministic update. You are right about the CTE, already tried it as Luis suggested and it worked. — LaUnicaMilos, Feb 08 '19 at 20:40
Glad you got a solution. Is this part of a batch update type of thing? That is about the only reason I can see where you don't really care which ones get updated. — Sean Lange, Feb 08 '19 at 20:58
Yes. It's a batch sync of Inventory plus a migration. Long story short: there are some sales in an old system that are not reflected in the new system. This old system is going to be decommissioned, so before shutting it down we need to have the correct amount of inventory available in the new one. — LaUnicaMilos, Feb 08 '19 at 21:08

Luis Cazares · Accepted Answer · 2019-02-08T20:16:05.610

2

You could use ROW_NUMBER to enumerate the inventory items that you need to update.

WITH cteProducts AS(
    SELECT product.ProductID, sales.CartID, COUNT(sales.ID) AS QuantitySold
    FROM Products product
    INNER JOIN Sales sales ON sales.ProductID = product.ProductID
    WHERE <conditions> 
    GROUP BY product.ProductID, sales.CartID
),
cteInventory AS(
    SELECT *,
        ROW_NUMBER() OVER( PARTITION BY ProductID ORDER BY (SELECT NULL)) AS rn /*Change the ORDER BY for an actual column if needed, probably for FIFO*/
    FROM Inventory
    WHERE i.Converted = 0 
    AND i.CartID IS NULL 
)
UPDATE i
SET i.Converted = 1,
    i.CartID = q.CartID,
    i.ReservedDate = GETDATE()
FROM cteInventory i
INNER JOIN cteProducts q ON q.ProductID = i.ProductID
WHERE i.rn <= q.QuantitySold;

edited Feb 08 '19 at 20:16

answered Feb 08 '19 at 20:11

Luis Cazares

3,495
8
22

Ha, almost identical to mine. +1 – Thom A Feb 08 '19 at 20:12
Actually, I'd recommend putting `i.Converted = 0 AND i.CartID IS NULL` within the `WHERE` of `cteInventory`. Otherwise you might end up having the rows with `Converted = 1` with the lower values for `RN`. – Thom A Feb 08 '19 at 20:14
Thank you @LuisCazares and @Larnu! It seems to work in my simple test scenario. I will try it on the real case now and see how it does. I would love to give you points for your answer but apparently my reputation is too low. :( – LaUnicaMilos Feb 08 '19 at 20:38

Is there a way to UPDATE TOP (N) with inner join where N is a field of such inner join?

1 Answers1