mysql update and get max(uploadDatetime)

I have a query and I want to get the max datetime for the update does anyone know how I can do this

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

Open in new window

LVL 6
r3nderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
SimonConnect With a Mentor Commented:
From what I can make out from your query (without table definitions) it looks like the UPDATE can be simplified to avoid the joins and just use a subquery to get the max(insertdate) for that item.

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.
0
 
r3nderAuthor Commented:
Thanks Simon worked great
0
Question has a verified solution.

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.

All Courses

From novice to tech pro — start learning today.