Tengo 3 mesas.

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    |

ACTUALIZACIÓN El conjunto de la tabla de productos de información del producto. La tabla de inventario contiene información sobre los lugares donde están disponibles los productos. Un producto puede tener inventarios múltiples y un producto no puede tener inventarios. La tabla de ubicaciones tiene una lista única de todas las ubicaciones posibles. La fecha de revisión en la tabla de ubicación a menudo se actualiza.

Quiero actualizar la fecha de revisión en la tabla de productos para cada ID de producto y seleccionando el máximo (review_date) de la tabla de ubicación para cada ID de producto. Porque un producto puede tener múltiples inventarios y ubicaciones asignados. Quiero la fecha reciente en que se actualiza la ubicación del producto.

Resultado esperado

Producto de mesa

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  

Intenté algo como esto de diferentes maneras, pero parece que no lo entiendo. Cualquier ayuda apreciada. TIA

0
Sakthivel 13 sep. 2018 a las 15:51

3 respuestas

La mejor respuesta

Parece que está uniendo la tabla de ubicación a la tabla de inventario en dos piezas de información diferentes. (ID de ubicación e ID de producto) Si LocationID en la tabla de inventario es una ID de ubicación y no una fecha (como en su ejemplo), intente esto. (No probado)

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  

Además, creo que tendrá que ordenar por Location_ID para reunir todas las ubicaciones, luego elegir la fecha más alta. No lo he probado, por lo que la función agregada de TOP podría no permitirte hacer esto.

1
Sakthivel 13 sep. 2018 a las 13:39

Primero, nunca use comas en la cláusula FROM. Siempre use la sintaxis adecuada, explícita, estándar JOIN.

En segundo lugar, puede hacer esto con APPLY. El problema es la repetición de la tabla Product. Necesita una condición de correlación para que esto funcione como espera. Pero no hay correlación entre la subconsulta y la tabla que se actualiza, por lo que todos obtienen el mismo valor.

Así que:

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;

También puede hacer esto usando GROUP BY. Existe una buena posibilidad de que con los índices correctos, APPLY sea más rápido.

0
Gordon Linoff 13 sep. 2018 a las 13:45

Si miras esto de esta manera. Tiene su tabla de productos y tiene la combinación de inventario y ubicación. Puede hacer esto con una subconsulta o tratar de resolverlo con una expresión de tabla común DOCUMENTOS DE MS CTE

Esto se vería algo así

  • Calcule la última fecha de revisión para cualquier producto en el inventario.
  • Actualizar esos productos en Producto

Usar un CTE sería algo así.

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
1
user1694674 13 sep. 2018 a las 13:18