Update table with top 1 field from other table-CodePudding

I've been fighting this for a few days. I have an inventory table based on a barcode field. I also have a location table that has multiple entries as this item may have been moved multiple times. I want to add a current_location field in the inventory table that shows the location the item was moved to last. So, I want the location_to field from the location table with the most recent date_modified. I can't quite get my head around this.

So the inventory table has (only one entry per barcode):

barcode=xxx current_location = null
barcode=yyy current_location = null

The location table has (multiple entries per barcode):

barcode = xxx, location_to = 1, datemodified = 01/01/2022
barcode = xxx, location_to = 2, datemodified = 01/02/2022
barcode = xxx, location_to = 3, datemodified = 01/03/2022
barcode = yyy, location_to = 4, datemodified = 01/04/2022
barcode = yyy, location_to = 5, datemodified = 01/05/2022
barcode = yyy, location_to = 6, datemodified = 01/06/2022

I want to set the inventory.current_location = 3 for xxx and 6 for yyy as those are the most recent via datemodified.

I have tried something like this:

update Inventory
set current_location = 
L.location_to 
from Inventory I
inner join Location L on I.Barcode = L.Barcode
where L.DateModified = (select top 1 L.DateModified where I.Barcode = L.Barcode order by L.DateModified DESC)

But this returns the same current_location for xxx as for yyy as the top one is not specified per barcode.

I hope this makes some sense as my brain is fried and I can't quite get my head around it. I hope the initial description of the problem is enough because I think the code I am attempting is wrong and is just confusing the entire issue.

Thank you for looking at this. I know some of you will see this as an easy problem and once I see the solution, I probably will as well.

CodePudding user response：

There's a few issues with your query, not least your subquery is missing from location. From your use of top 1 you're probably using SQL Server.

You could perform the update using a CTE such as:

with i as (
    select i.current_location, l.Location_to
    from Inventory i
    cross apply (
        select top (1) l.Location_to
        from location l
        where l.Barcode = i.Barcode 
        order by l.DateModified desc
    )l
)
update i set current_location = Location_to;

CodePudding user response：

Assuming you are using SQL Server, since you've put TOP 1 in there, then this should work:

UPDATE Inventory
SET current_location = L.location_to 
FROM Inventory I
INNER JOIN (
            SELECT Barcode, location_to, ROW_NUMBER() OVER (PARTITION BY Barcode ORDER BY DateModified DESC) AS rownum 
            FROM [Location]
            ) L on I.Barcode = L.Barcode
WHERE L.rownum = 1;

CodePudding user response：

I prefer Stu's query, but if you wanted to "fix" your query, this should work.

Notice adding location as L2 in the DateModified subquery.

update Inventory
set current_location = 
L.location_to 
from Inventory I
inner join Location L on I.Barcode = L.Barcode
where L.DateModified = (select top 1 L2.DateModified from [location] L2 where I.Barcode = L.Barcode order by L2.DateModified DESC)

CodePudding user response：

Straightforward is a subselect with subselect, working with every DBMS:

UPDATE Inventory i SET current_location = (
      SELECT l1.location_to from Location l1
       WHERE l1.barcode = i.barcode and 
             l1.datemodified = (SELECT max(l2.datemodified) from Location l2
                                 WHERE l2.barcode = i.barcode))

DB Fiddle