Prev: Could someone please say something on my issue?
Next: user exit problem
From: Lennart on 5 Oct 2006 15:03

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

From: mirof007 on 5 Oct 2006 16:24
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

From: Tonkuma on 6 Oct 2006 08:11
> 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;

From: mirof007 on 6 Oct 2006 13:11
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

From: Lennart on 6 Oct 2006 15:21

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(a)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

[...]

 |  Next  |  Last
Pages: 1 2
Prev: Could someone please say something on my issue?
Next: user exit problem