Link to home
Start Free TrialLog in
Avatar of r3nder
r3nderFlag for United States of America

asked on

update with a select

I am trying to convert this from mysql to sql can someone help?
UPDATE Temp_Parts tp          
  JOIN (SELECT inventorylistid,                           
  locationid,                            
  Max(insertdate) AS MaxInsertDate         
  FROM   Inventory_PartsHistory          
  GROUP  BY inventorylistid,                    
  locationid) D               
  ON D.inventorylistid = tp.inventorylistid                 
  AND D.locationid = tp.locationid  
  SET    tp.insertdate = D.maxinsertdate   
  WHERE  D.maxinsertdate IS NOT NULL;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ste5an
ste5an
Flag of Germany image
E.g.

UPDATE	tp
SET		insertdate = D.maxinsertdate  
FROM	Temp_Parts tp          
	INNER JOIN (
		SELECT	inventorylistid,                           
				locationid,                            
				Max(insertdate) AS MaxInsertDate         
		FROM	Inventory_PartsHistory   
		WHERE	maxinsertdate IS NOT NULL       
		GROUP  BY inventorylistid,                    
			locationid
		) D ON D.inventorylistid = tp.inventorylistid AND D.locationid = tp.locationid;

Open in new window

Avatar of r3nder
r3nder
Flag of United States of America image

ASKER

thanks Kyle