SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 UPDATE with RIGHT JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 01/14/2013 :  16:07:05  Show Profile  Reply with Quote
I am not sure how to structure UPDATE query. I am trying to obtain MAX on date and then pass that value to UPDATE statement. Not sure how to include JOIN in UPDATE or perhaps consolidate all to 1 UPDATE statement. Please advise. Thank you.


create procedure [dbo].[sp_WOSP_Updates]


as
begin


--variables
DECLARE @HWD_ARRIV_DT smalldatetime

/****************************************************************************/

//returns only 1 date
SET @HWD_ARRIV_DT =
(SELECT MAX(w.COMPLETED_TIME) as COMPLETED_TIME FROM view_WOS w
RIGHT JOIN Portfolio p
ON p.SERVER_NAME = w.[HOST_NAME]
WHERE w.SUB_REQUEST = 'Receive Hardware'
AND p.P_ID=w.P_ID )

UPDATE Portfolio
SET HWD_ARRIV_DT = @HWD_ARRIV_DT
WHERE Portfolio.P_ID = w.P_ID
AND Portfolio.SERVER_NAME = w.[HOST_NAME]
AND w.SUB_REQUEST = 'Receive Hardware'

Edited by - nietzky on 01/14/2013 16:08:16

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/14/2013 :  17:51:04  Show Profile  Reply with Quote
UPDATE p
SET HWD_ARRIV_DT = COMPLETED_TIME
FROM Portfolio p
INNER JOIN
(select w.HOST_NAME,w.P_ID,MAX(w.COMPLETED_TIME) as COMPLETED_TIME
from view_WOS w
where w.SUB_REQUEST = 'Receive Hardware'
group by w.HOST_NAME,w,P_ID
) w ON p.SERVER_NAME = w.HOST_NAME and p.P_ID = w.P_ID

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 01/15/2013 :  10:02:07  Show Profile  Reply with Quote
Thank You - it works!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000