Thread: DB2: Update & Temporary Tables
03-30-06, 18:38 #1Registered User
- Join Date
- Mar 2006
Unanswered: DB2: Update & Temporary Tables
Using db2 v 126.96.36.199 Fixpack 0 Windows XP SP2
Basically I want to run an update and using one statement I want to reuse data from a subquery.
So I have something like this currently:
update parent p
set p.field1 = case
when p.field2 = 1 and (select c.field1 from child c) = 'A'
when p.field2 = 2 and (select c.field1 from child c) = 'A'
This is obviously dumbed down, but the point is that I want to reuse the data from the (select c.field1 from child c) query so that I don't need to re-run it all the time and I want to be able to do it one command.
Is there a way, using updates, to do this? I know that with inserts you can use WITH VALUES for something like that, but that doesn't seem to work and I'm having difficulty finding the documentation for it - but something like how WITH VALUES works would work sufficiently. Any ideas?
03-30-06, 22:22 #2Registered User
- Join Date
- May 2003
I am not sure in what fixpack that feature was added to DB2.
Regardless of features, each fixpack averages more than 300 fixes each, and we are now on fixpack 11, so that means IBM has fixed over 3,300 bugs since the version 8.1 base code (fixpack 0) was released. Fixpacks are free.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390