Solved

mysql update and get max(uploadDatetime)

Posted on 2015-01-05
2
385 Views
Last Modified: 2015-01-06
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
Comment
Question by:r3nder
2 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40532575
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
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40533506
Thanks Simon worked great
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now