Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 UPDATE with RIGHT JOIN

Author  Topic 

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-01-14 : 16:07:05
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'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-14 : 17:51:04
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

75 Posts

Posted - 2013-01-15 : 10:02:07
Thank You - it works!
Go to Top of Page
   

- Advertisement -