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 minimal reproducible example--that includes a precise specification & desired output for given input. — philipxy
– philipxy, Commented Sep 13, 2018 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
– philipxy, Commented Sep 13, 2018 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
– philipxy, Commented Sep 14, 2018 at 4: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? — philipxy
– philipxy, Commented Sep 14, 2018 at 4:50

Sakthivel · Accepted Answer · 2018-09-13 13:39:57Z

1

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.

edited Sep 13, 2018 at 13:39

Sakthivel

1,9382 gold badges23 silver badges48 bronze badges

answered Sep 13, 2018 at 13:06

user3407753

Sign up to request clarification or add additional context in comments.

Comments

user1694674 · Accepted Answer · 2018-09-13 13:18:32Z

1

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

answered Sep 13, 2018 at 13:18

user1694674

1265 bronze badges

2 Comments

Sakthivel Over a year ago

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.

user1694674 Over a year ago

He asked for the MAX(Review_date) for each product.

Gordon Linoff · Accepted Answer · 2018-09-13 13:45:40Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related