Updating CommonTable Expression

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

General SQL Server Forums

New to SQL Server Programming

Updating CommonTable Expression

Author

Topic

hraj
Starting Member

1 Post

Posted - 2011-02-03 : 11:22:22

Is it possible to fire UPDATE on a CTE?

e.g. if I have a CTE names var as below,

with var as (

select 
2 as numeric_val,
'C' as char_val,
4 as id

from dual

)

Now if I wish to update the numeric_value column with value 5, is there any way to do it?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-04 : 05:08:42

Looks senseless.
Try it

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-04 : 07:03:38

Is this Oracle? There is no such thing as "dual" in sql server...and just as a reminder this is a forum for sql server only.

That being said; your query doesn't make any sense because you can't run an update and a select on the same CTE. It is however possible to update the underlaying table of a CTE:

DECLARE @table table (ID int, Name varchar(100))
INSERT INTO @table VALUES (1, 'Lumbago'), (2, 'hraj'), (3, 'webfred')

;with cte as (
	select * from @table where ID < 3
	)
update cte set Name = Name + '_updated'

select * from @table

- Lumbago
My blog-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources