How to update from a join table

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 2000 Forums

SQL Server Development (2000)

How to update from a join table

Author

Topic

JasonD
Starting Member

6 Posts

Posted - 2005-11-29 : 10:30:16

How do I update a column on one table but I need to join two tables togther to get the required rows.

I tried this SQL below on MS2000 server but it gives me the following error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'PSWORKLIST'.

UPDATE PSWORKLIST SET INSTSTATUS = 3
FROM PS_JWS_JOBAUTH_WL
WHERE INSTATUS IN (0,1)
PSWORKLIST.BUSPROCNAME = PS_JWS_JOBAUTH_WLB.USPROCNAME
AND PSWORKLIST.ACTIVITYNAME = PS_JWS_JOBAUTH_WL.ACTIVITYNAME
AND PSWORKLIST.EVENTNAME = PS_JWS_JOBAUTH_WL.EVENTNAME
AND PSWORKLIST.WORKLISTNAME = PS_JWS_JOBAUTH_WL.WORKLISTNAME
AND PSWORKLIST.INSTANCEID = PS_JWS_JOBAUTH_WL.INSTANCEID
AND PSWORKLIST.TRANSACTIONID = PS_JWS_JOBAUTH_WL.TRANSACTIONID
AND EMPLID='0004466'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-29 : 11:00:36

try this:


UPDATE wl set
	wl.INSTSTATUS = 3
from	PSWORKLIST wl
join	PS_JWS_JOBAUTH_WL ja
		on ja.USPROCNAME = wl.BUSPROCNAME
		and ja.ACTIVITYNAME = wl.ACTIVITYNAME
		and ja.eventname =  wl.eventname
		and ja.worklistname = wl.worklistname
		and ja.INSTANCEID = wl.INSTANCEID
		and ja.TRANSACTIONID = wl.TRANSACTIONID
where	INSTATUS IN (0,1)
and	EMPLID='0004466'

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources