UPDATE with RIGHT JOIN - SQL Server Forums

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

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-01-15 : 10:02:07

Thank You - it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources