question about "select * from new table (update ...)"

[Lennart]

I really like the construction:

select * from new table (update ....) X

but I noticed that it cant be used as:

insert into T select * from new table (update ....) X

because of:

SQL20165N An SQL data change statement within a FROM clause is not
allowed
in the context in which it was specified. SQLSTATE=428FL
Anyone (Serge ?) know if there are any plans for loosing up this
restriction in the future, or if it will remain this way?

The other day I had a task updating all rows fullfilling a certain
condition and for each row updated, insert or update (dependent of
existence) another row. A had a depression for 10 minutes when I
discovered I couldnt do it as ;-)

merge into ... using (select ... from new table (update ....) X on
.... when matched ... when not matched ...
/Lennart

[mirof007]

It's possible today. Try the following:

with tmp1 as (select * from new table(insert into t1 values 1,2,3)
tmp3),
tmp2 as (select * from new table(insert into t2 select * from tmp1)
tmp4)
select count(*) from tmp4

Similar should work for update. Basically, we force the user to specify
the exact order of operations by putting them into Common Table
Expressions, to remove any semantic ambivalence wrt the order of the
execution of the operations.

Hope this helps,
Miro

[Tonkuma]

with tmp1 as (select * from new table(insert into t1 values 1,2,3)

tmp3),
tmp2 as (select * from new table(insert into t2 select * from tmp1)
tmp4)
select count(*) from tmp4

Why is tmp2 necessary?
Is this not enough?
with tmp1 as (select * from new table(insert into t1 values 1,2,3)
tmp3)
select * from new table(insert into t2 select * from tmp1) tmp4;

[mirof007]

I'm not 100% certain, but I think DB2 simply disallows a data change
table reference in INSERT, UPDATE and DELETE today. So in principle,
you're right, tmp2 is not necessary, it's there just to get around the
current restriction.

Regards,
Miro

[Lennart]

mirof007 wrote:

It's possible today. Try the following:

with tmp1 as (select * from new table(insert into t1 values 1,2,3)
tmp3),
tmp2 as (select * from new table(insert into t2 select * from tmp1)
tmp4)
select count(*) from tmp4

Ah, yes that will do. A little typo though, I think it should be:

with tmp1 as (select * from new table(insert into t1 values 1,2,3)
tmp3),
tmp2 as (select * from new table(insert into t2 select * from tmp1)
tmp4)
select count(*) from tmp2

Also, I think the 2 CTE is unnecessary, because the following works:

# create table
db2 "create table T1 (c1 int not null primary key, c2 int not null
default 0)"
# some init data
db2 "insert into T1 (c1) with T (c1) as (values 0 union all select c1+1
from T where c1 < 5) select c1 from T"
# add some more
db2 "with tmp1 as (select * from new table(update t1 set c2 = c2+1
where mod(c1,2) = 0) tmp3) select * from new table(insert into t1
select c1+10,c2 from tmp1) tmp4"
C1 C2
----------- -----------
10 1
12 1
14 1

[ltjn@lelles ~/install/ddl/bin]$ db2 "select * from T1"

C1 C2
----------- -----------
0 1
1 0
2 1
3 0
4 1
5 0
10 1
12 1
14 1

Next step will be to squeze in the merge, now that will be one hell of
a stmt :-)

Thanx a lot for pointing this out
/Lennart

[...]

[Serge Rielau]

Squeezing in MERGE won't work in the same way.
It's the "missing piece".
Much to my dismay SELECT FROM MERGE is not support at present, and WITH
can't precede MERGE.
You can consider using a dynamic compound statement or simply wrapping
the MERGE into an SQL Table function in combination to what Miro proposed.
That should work very well in a non DPF environment.
Make sure to check the db2exfmt.

On the topic of allowing stacked updates those who heard me IBM panel at
IDUG Europe Thursday know that I would like to reduce complexity of SQL
rather than add more to it.
The WITH clause allows for a clean way to semantically serialize these
constructs. Allowing for stacking and joins between updates get very
quickly very unreadable.
We prefer leaving the pipelining to the optimizer.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/