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

Question

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

This does not fully say what you want. You give a wrong query & vaguely say "update the review date in product table for each row" "based on product id from inventory table and review date from location table". Please explain exactly what you want that you are vaguely describing via "based on". Do you want replace the time of a product that appears with some locations by the most recent of the times of the locations? Always use enough words, phrases & sentences to be clear & precise. Please also read & act on [mcve]--that includes a precise specification & desired output for given input. — philipxy, Sep 13 '18 at 16:26
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. Please read & act on hits googling 'stackexchange homework'. — philipxy, Sep 13 '18 at 16:28
Expected result--good. The added table content descriptions are so general they're not particularly helpful; the names of the tables & columns would be assumed to say that much. The only way constraints affect querying is some queries will return the same values as others they wouldn't otherwise; constraints are not needed to query; so the additions re cardinalities don't help. The info you need to give is how output is a function of inputs or base table & query meanings--what base table & query result rows say about the situation. You do that next though. ... — philipxy, Sep 14 '18 at 04:45
"update the review date in the product table for each product ID"--OK. "and selecting the max(review_date) from location table"--not clear. See my first comment for my guess. The way to clearly give a query meaning is to give a statement template in terms of its columns where the tuples that make a true statement from the template by supplying values for the columns go in the table & the others stay out--the query result's *(characteristic) predicate*. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Sep 14 '18 at 04:50
PS I said this seems to be a faq. I googled my comment's guess (cut & paste--even including the specific business words product, etc) with 'sql site:stackoverflow.com' & got apparent answers. Do you? When you come up with clear phrasing--sufficient to give someone & get back the query you need--see what googling per my faq/googling comment gives you. — philipxy, Sep 14 '18 at 04:57
@philipxy Thanks, I actually have 12 tables and a lot of joins. I was pretty confused myself and instead of waiting to write a perfect question, I'd write based on my understanding and update the question based on other's inputs and comments. — Sakthivel, Sep 18 '18 at 13:52

score 1 · Accepted Answer · edited Sep 13 '18 at 13:39

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.

score 1 · Answer 2 · answered Sep 13 '18 at 13:18

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

This returns all right products with one max date. but, Product Ids in inventory have different locations assigned to it and thus different review date. Updated the question. — Sakthivel, Sep 14 '18 at 04:45

score 0 · Answer 3 · answered Sep 13 '18 at 13:45

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.

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

3 Answers3