DROP Table if exists TEMP_PARTS;
CREATE TABLE TEMP_PARTS(SELECT il.PartDesc as 'Tool Type', il.PartNumber, ip.Quantity, Cast('2012:01:01 12:00:00 AM'as DateTime) AS 'InsertDate', ip.InventoryListID, ip.locationID
FROM Inventory_Parts as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID
WHERE LocationID = 1 and isVisible = 1);
UPDATE TEMP_PARTS tp
JOIN Inventory_InventoryList as il ON il.UID = tp.InventoryListID
JOIN Inventory_PartsHistory ph ON ph.InventoryListID = il.UID
//////I want tp.InsertDate = MAX(ph.InsertDate) WHERE/////////////////////////////////
Set tp.InsertDate = ph.InsertDate WHERE tp.PartNumber = il.PartNumber AND tp.LocationID = ph.locationID);
SELECT * FROM TEMP_PARTS
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
UPDATE TEMP_PARTS tp
Set tp.InsertDate = (select max(InsertDate) from inventory_PartsHistory WHERE InventoryListID = tp.InventoryListID AND locationID=tp.LocationID);
Even if you DO need the join to Inventory_InventoryList, you'd still use a subquery to get the max(insertdate) from the Inventory_PartsHistory table.