Reputation: 2162
Hello I want to Update 1 table based on value present in other table. I could use Join to write the update statement and infact I have written it and it works using Join. But out of curiosity I want to use CTE. I wrote the following query but it doesn't seem to work. Can anybody tell me what the issue is? Does CTE require Select statement at the end compulsorily? Why can I not write Update statement?
WITH cte(uid, col1)
As
(
Select uid, col1
From [User]
)
Update t2
Set col1 = cte.col1
Where uid = cte.uid
Upvotes: 1
Views: 375
Reputation: 107247
You still need to join back to your CTE, like so
WITH cte([uid], col1)
As
(
Select [uid], col1
From [User]
)
Update t2
Set col1 = cte.col1
FROM t2 inner join cte cte
on t2.[uid] = cte.[uid]
Edit I guess you could avoid the 'JOIN' keyword by using old-style SQL WHERE joins, but this isn't really good practice.
...
FROM t2, cte cte
WHERE t2.[uid] = cte.[uid]
Also note that you can also update through the CTE, like views, as long as you only update one table.
WITH cte(userCol1, t2Col1)
As
(
Select [u].col1 as userCol1, t2.col1 as t2Col1
From [User] u
inner join t2
ON u.[uid] = t2.[uid]
)
Update cte
Set t2Col1 = userCol1
Upvotes: 1