DB2 Recursive Query, with update

[cheste...@yahoo.co.uk]

I would like to do a recursive query and use the results to do an
update on a related table. .. is this possible..?

DB2 V9.x doesn't like it...

The standard recursive select works, but is an update also
possible.. ?

Based on this example...

WITH RPL (ID, PART, SUBPART, QUANTITY) AS
( SELECT ROOT.ID, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT CHILD.ID, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
)
SELECT DISTINCT PART, SUBPART, QUANTITY
FROM RPL
ORDER BY PART, SUBPART, QUANTITY

I would like to do this ...

WITH RPL (etc etc ...

.PART)

UPDATE OTHERTABLE O SET COLUMNA = X WHERE O.LINKID in (SELECT ID from
RPL)

So replace the final select with an update statement that uses results
of the recursive query.

thanks in advance for any pointers.

[Serge Rielau]

Matter of fact: You can.
DB2 doesn't support CTE's for anything else than SELECT and in INSERT
subqueries.
However, who says an UPDATE isn't a SELECT?
WITH v AS (<blah>)
SELECT COUNT(*) FROM OLD TABLE(UPDATE ...)

Voila :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[The Boss]

May I suggest that the sequel to your "SQL On Fire!" should be named "SQL
From Hell!" ?... ;-)

--
Jeroen

[Serge Rielau]

The Boss wrote:
> May I suggest that the sequel to your "SQL On Fire!" should be named "SQL
> From Hell!" ?... ;-)

I did propose "The dark side of SQL", but IDUG rejected it. :-(

[maddin...@googlemail.com]

I know the topic is very old but I tried to use the example but I could not resolve the following problem:

with tbcl (gde_nr_gen, gde_sl_tbcreator, gde_sl_tbname, gde_sl_tbclname, gde_nr_tbclcolseq) AS (
* some recursive stuff
)
select count(*) from final table (
update gdetbcl t1 set gde_nr_tbclcolseq =
(
select t2.gde_nr_tbclcolseq
from tbcl t2
where t1.gde_nr_gen = t2.gde_nr_gen
and t1.gde_sl_tbcreator = t2.gde_sl_tbcreator
and t1.gde_sl_tbname = t2.gde_sl_tbname
and t1.gde_sl_tbclname = t2.gde_sl_tbclname
)
where t1.gde_nr_gen = <..>
);

Error: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=GDET.TBCL, DRIVER=3.53.95
SQLState: 42704
ErrorCode: -204

So the update can't find my CTE.

May be someone is watching the thread and has a solution?

Best regards,

Martin

[Lennart Jonsson]

All I can think off is using a session table and store the result from
the cte, i.e.

declare global temp table T (...);
insert into T (...)
with ...

update gdetbcl t1
set gde_nr_tbclcolseq = ( select ... from session.T ...)
where t1.gde_nr_gen = <..>


/Lennart