SQL Server Forums - UPDATE with RIGHT JOIN

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

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

Topic

nietzky
Yak Posting Veteran

63 Posts

Posted - 01/14/2013 : 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'

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

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts

Posted - 01/14/2013 : 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

63 Posts

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

Thank You - it works!

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.05 seconds.