CODEWITHSUNDEEP
sqlsql-serverjoinsql-update
Sakthivel
Sakthivel

Reputation: 1938

Update SQL Server Table Row by row from values of other table with joins

I have 3 tables.

Table Product

Product_ID | Review_date |
1          | 01/01/2018    |
2          | 01/01/2018    |
3          | 01/01/2018    |
4          | 01/01/2018    |

Table Inventory
Inventory_ID  | Product_ID  | Location_ID
1             |        2    | 1    |
2             |        2    | 3    |
3             |        3    | 4    |
4             |        1    | 4    |

Table Location
Location_ID| Review_date |
1          | 04/02/2018    |
2          | 06/03/2018    |
3          | 01/05/2018    |
4          | 08/28/2018    |

UPDATE The product table set of product information. The inventory table has information about places where the products are available, One product can have multiple inventories and a product can have no inventories. The location table has unique list of all the possible locations. The review date in the location table is often updated.

I want to update the review date in the product table for each product ID and selecting the max(review_date) from location table for each product ID. Because a product can have multiple inventories and locations assigned to it. I want the recent date the product's location is updated.

Expected result

Table Product

Product_ID | Review_date |
1          | 08/28/2018    |  this prod id in inventory has loc id 4. 
2          | 04/02/2018    |  two inv records for the product so max date
3          | 08/28/2018    |
4          | 01/01/2018    |  no inv record. so leave it as such


UPDATE  P
SET     P.review_date = L.Inventory_review_date
FROM    Product AS P
CROSS APPLY
        (
        select  top 1 inventory_review_Date
        from    Location as L, Inventory as I, PRODUCT as P
        where   L.Location_ID = I.Inventory_ID and P.Product_ID = I.Product_ID
        order by
                L.Inventory_Review_date desc
        ) as L

I tried something like this in different ways but i dont seem to get it. Any help appreciated. TIA

Upvotes: 0

Views: 96

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

First, never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Second, you can do this with APPLY. The problem is the repetition of the Product table. You need a correlation condition for this to work as you expect. But there is no correlation between the subquery and the table being updated, so all get the same value.

So:

UPDATE P
    SET review_date = L.Inventory_review_date
    FROM Product P CROSS APPLY
         (SELECT TOP (1) L.inventory_review_Date
          FROM Location L JOIN
               Inventory I
               ON L.Location_ID = I.Inventory_ID 
          WHERE P.Product_ID = I.Product_ID
          ORDER BY L.Inventory_Review_date DESC
        ) L;

You can also do this using GROUP BY. There is a good chance that with the right indexes, APPLY will be faster.

Upvotes: 0

user3407753
user3407753

Reputation:

It looks like you're joining the location table to the inventory table on two different pieces of informaiton. (location id and product id) If LocationID in the Inventory table is a location ID and not a date (as in your example), try this. (Not tested)

UPDATE  P
SET     P.review_date = L.Inventory_review_date
FROM    Product AS P
CROSS APPLY
        (
        select  top 1 inventory_review_Date
        from    Location as L, Inventory as I, PRODUCT as P
        where   L.Location_ID = I.Location_ID and P.Product_ID = I.Product_ID
        order by
                L.Inventory_Review_date desc
        ) as L

Also, I would think that you are going to have to order by Location_ID to get all locations together, then choose the top date. I haven't tried it, so the aggregate function of TOP might not let you do this.

Upvotes: 1

user1694674
user1694674

Reputation: 126

If you look at this in this way. You have your product table and you have the combination of inventory and location. You can do this with a subquery or try to figure it out with a Common Table Expression MS CTE DOCS

This would look something like

  • Figure out the last review date for any product in Inventory.
  • Update those products in Product

Using a CTE it would be something like.

WITH inv_loc_cte AS
(
Select i.Product_id, max(l.Review_date)
    from Inventory i 
    inner join [Location] l on i.Location_id = i.Location_id
    Group by i.Product_id
)
UPDATE p
SET Review_date = c.Review_date
FROM Product p
INNER JOIN inv_loc_cte c on p.Product_id = c.Product_id

Upvotes: 1

Related Questions