September 18, 2009 at 9:27 am
Thanks to anyone who can help. Below are tables, sample data and my attempt at an update statement. I want to update #Manufacturers.Manufacturer_Date with the oldest record in the #ProductInfo table where the product is manufactured by that manufacturer. The #ProductInfo table is linked to the #Manufacturers table through the #Products table.
The updated #Manufacturers table would look like:
ManufacturerID,Manufacturer_Date
1,1/1/2009
2,2/15/2009
3,1/15/2009
CREATE TABLE #Manufacturers (ManufacturerID int,Manufacturer_Date datetime)
CREATE TABLE #Products (ProductID char(1),ManufacturerID int)
CREATE TABLE #ProductInfo (ProductID char(1),Product_Date datetime)
INSERT #Manufacturers (ManufacturerID)SELECT 1 UNION SELECT 2 UNION SELECT 3
INSERT #Products (ProductID,ManufacturerID)SELECT 'A',1 UNION SELECT 'B',1 UNION SELECT 'C',2 UNION SELECT 'D',3
INSERT #ProductInfo (ProductID,Product_Date)SELECT 'A','1/1/2009' UNION SELECT 'B','2/1/2009' UNION SELECT 'C','2/15/2009' UNION SELECT 'D','1/15/2009'
SELECT * FROM #Manufacturers
SELECT * FROM #Products
SELECT * FROM #ProductInfo
UPDATE #Manufacturers
SET Manufacturer_Date = MIN(#ProductInfo.Product_Date)
FROM #Manufacturers
INNER JOIN #Products ON #Manufacturers.ManufacturerID = #Products.ManufacturerID
INNER JOIN #ProductInfo ON #Products.ProductID =#ProductInfo.ProductID
DROP TABLE #Manufacturers
DROP TABLE #Products
DROP TABLE #ProductInfo
September 18, 2009 at 9:34 am
It looks like you have missed out a select on the update statement
UPDATE #Manufacturers
SET Manufacturer_Date = MIN(#ProductInfo.Product_Date)
FROM #Manufacturers
INNER JOIN #Products ON #Manufacturers.ManufacturerID = #Products.ManufacturerID
INNER JOIN #ProductInfo ON #Products.ProductID =#ProductInfo.ProductID
should be;
UPDATE #Manufacturers
SET Manufacturer_Date = (SELECT MIN(#ProductInfo.Product_Date)
FROM #Manufacturers
INNER JOIN #Products ON #Manufacturers.ManufacturerID = #Products.ManufacturerID
INNER JOIN #ProductInfo ON #Products.ProductID =#ProductInfo.ProductID)
September 18, 2009 at 9:55 am
That updates all manufacturers with the same MIN date in the #ProductInfo table
I need the MIN date per product.
The updated #Manufacturers table would look like:
ManufacturerID,Manufacturer_Date
1,1/1/2009
2,2/15/2009
3,1/15/2009
September 18, 2009 at 10:14 am
hello emily-1119612
why dont you try this
UPDATE #Manufacturers
SET Manufacturer_Date = B.MIN_PRODUCT_DATE
FROM #Manufacturers
INNER JOIN #Products ON #Manufacturers.ManufacturerID = #Products.ManufacturerID
INNER JOIN
(
select ProductId, MIN(Product_date) MIN_PRODUCT_DATE
from #productinfo
GROUP BY ProductId
) B
ON #Products.ProductID =B.ProductID
September 21, 2009 at 8:35 am
Yes, this is what I am looking for. Thanks.
September 21, 2009 at 10:00 am
anytime 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply