Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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

0
r3nder
Asked:
r3nder
1 Solution
 
SimonCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now