Joining a table to a CTE

[Pho3nix]

Hi Ho,

What am I trying to do. Union 2 tables, update the result set and get that result set back. This works fine in a stored proc when I have temp tables but um yeah. A view is a differant story.
Can't reference the column from the table I've joined to the CTE as it's been referenced too many times :wtf:

Before we begin,

If isn't my design, I just have to get it working.
Don't ask :twisted:

Code Extract can be provided on request.

Anyone have any experience with this?
Alternatives would be appreciated.

 

[oomjan]

Not sure what you are trying to do. What are trying to do in the view?
Have you tried using a table udf?

 

[Pho3nix]

Not sure what you are trying to do. What are trying to do in the view?
Have you tried using a table udf?

Union 2 tables.
Update the result set and get that result set back.
This works fine in a stored proc when I have temp tables but um yeah.

This needs to be in a view as it is being used elsewhere...

 

[oomjan]

You will have to post some code..

;with MyData as
(
SELECT
Col1,
Col2
FROM
Table1

UNION ALL

SELECT
Col1,
Col2
FROM
Table2
)

INSERT @ReturnData
SELECT
Col1,
Col2
FROM
MyData

UPDATE @ReturnData
SET Col1 = 'whatava'

RETURN

 

[adinfinitum]

What SQL are you using?

 

[rrh]

I am probably misunderstanding something but the fact that you have defined a view over the tables should not impact your ability to perform an update.

You should be able to perform a nested statement, e.g.:

select statement from
(update
(union statement)
set statements)

My SQL may be a little rusty but I have done what you ask using DB2

 

[prod]

Why can't you update the result set itself?

select product as product , [stock_available_qty] + 1 as [updated_qty],
					[stock_available_qty] as [original qty]
from 
			(	select product ,[stock_available_qty] from 
				stock with (nolock)
				where branch=2

				union

				select product ,[stock_available_qty] from
				stock with (nolock)
				where branch=3
			) as p

 

[Pho3nix]

Update on this.
I was having a k@k day.

Solved it