I found this post
http://groups.google.ca/group/comp.d...5694324f276587
which sounds very promising, but I'd like a more explicit example.
That is, I don't follow how to encapsulate the update within the
select.

Could someone flesh it out somewhat?

Thanks.

Here's the link I tried referring to:

https://groups.google.com/group/comp...4f276587?hl=en

On 2011-11-20 17:59, helmuterik wrote:
Here's a trivial example that we can start discussing:

create table t1 ( x int not null );
insert into t1 (x)
with r (x) as (values 0 union all select x+1 from r where x<10)
select x from r;

Assume we would like to multiply all odd numbers in t1 with 10 and would
like to return the result.

with cte (x) as (
select x
from new table (
update t1
set x = 10 * x
where mod(x,2) = 1
) y (x)
) select x from cte;


X
-----------
10
30
50
70
90

select * from t1

X
-----------
0
10
2
30
4
50
6
70
8
90
10

in this trivial example it is of course not necessary to use a cte

select x
from new table (
update t1
set x = 10 * x
where mod(x,2) = 1
) y (x)

will do the same thing for you. If there are triggers involved you might
want to use final table instead of new table.

Did that help? I'm not sure what it is you would like to have explained,
so perhaps you can post an example of what it is you would like to solve?

/Lennart

On Nov 20, 6:58*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Many thanks for the prompt and illuminating reply.

What I want is something analogous to this:

(It is part of a trigger, so the table aliased as O in the example
below is the Old value).

update pb
set sts = 'new status' -- and some other values
where exists
(
select 1
from umx X, par A
where X . hashkey = O . hashkey
and X . repnbr = PB . rpnr and X . rrrn = PB . RRRN
and x.repnbr = a.repnbr and a.repsts = '12' and reptpe = '34'
) ;

Some of these tables contains A LOT of rows so the above works
technically, but not sufficiently efficient (particularly since
triggers are synchronous).
The subquery above performs horribly but in a normal select situation
with a CTE it would be very efficient.
What I would like is that the subquery is evaluated first and only
those rows are searched (i.e. materialization instead of composition).
But the WHERE EXISTS is not that smart and the performance is
infinitely worse.

So basically, and update with a similar syntax and performance like
this:

with p(a, b) as (
select repnbr, rownbr
from pa join umx x
on rpnr=repnbr
where reptpe = '34' and repsts = '12' and
x.hashkey=o.hashkey)
select * -- should be a searched update based on a and b columns
from p join pb using(a, b);

Thank you again.

On 2011-11-20 20:28, helmuterik wrote:
I see (I think :-) I don't think the technique discussed above will be
applicable in this case since all it does is to select from an update.

What is O refering to, i.e.

create trigger ...
after delete/update of ????

Meanwhile, it will be difficult for the optimizer to utilize any indices
on pb so it will probably scan the table (you can check the plan). Maybe
something like:

update pb
set sts = 'new status' -- and some other values
where (pb.rpnr, pb.RRRN)
= ( select X.repnbr, X.rpnr
from umx X
join par A
on x.repnbr = a.repnbr
where x.hashkey = O.hashkey
and a.repsts = '12'
and reptpe = '34'
);

will improve performance?


/Lennart

Thanks a lot, again.

I managed to find a decent performing solution still using the WHERE
EXISTS method (an additional WHERE repnbr IN subquery), but I will
look evaluate your proposal too. It's looks more neat.

Best wishes

On 2011-11-21 12:59, helmuterik wrote:
You might also want to have a look at if indices are missing. db2advis
will give usefull suggestions for this. You cant take it's word for
granted since it often suggests redundant indexes, but it is a very
helpful tool.


/Lennart