Update join after a CTE - 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 2008 Forums

Transact-SQL (2008)

Update join after a CTE

Author

Topic

ljkrauss
Starting Member

2 Posts

Posted - 2012-07-03 : 11:05:46

I have a cte that I am trying to reference in an update with a join in it. The CTE runs butit errors on the update in the reference to the CTE.

WITH cteK AS
(
SELECT a.NetworkID, A.EmployeeID, B.Path,A.Manager, a.GUIDPath FROM AD_Populate.dbo.AD_Changes A
LEFT JOIN AD_Populate.dbo.AD_Export B on ltrim(rtrim(upper(left(UserPrinciplename,(charindex('@',[UserPrinciplename])-1))))) = A.NetworkID
WHERE len(b.guidpath) > 0 and len(a.networkid) > 0
)
SELECT c.employeeid,d.path as ManagerPath, d.GUIDPath from cteK C
LEFT JOIN cteK D on c.manager = d.employeeid
WHERE len(c.employeeid ) > 0 and len(d.path) > 0
ORDER BY employeeid,ManagerPath

UPDATE A
Err==> SET A.Manager = cteK.ManagerPath
FROM [AD_Populate].[dbo].[AD_Changes] as A
LEFT JOIN cteK as K on A.EmployeeID = K.EmployeeID

Err==> invalid object name 'cteK'

Any ideas would be greatly appreciated.
SB

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-03 : 11:14:22

Only one statement after the cte (and using the cte) is possible.
SELECT or UPDATE but not both.

No, you're never too old to Yak'n'Roll if you're too young to die.