|
View previous topic :: View next topic
|
| Author |
Message |
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
Hi,
I need a quick suggestion/resolution, please guide.
I have declare a temporary table 'on commit preserve rows' and inserting a set of records. this records will be read and updated with new values in the same program. now the updated values are read/selected for further application. but the values updated are not reflected, only the values inserted initally are reflected.
I'm commiting the rows after every update but still the values doesn't reflect.
please suggest is their a way to commit the updated values in the temporary table. And does update works on temporary table?
Cheers,
Vinu |
|
| Back to top |
|
 |
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
As per the below article, it seems UPDATE doesn't work on Temporary tables. Instead, you can update the row in your program and then insert into the temp table.
www.craigsmullins.com/dbu_0802.htm |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Vinay,
According to the article Srihari references, UPDATE does work for a DECLARED temp table as opposed to a CREATED temp table:
| Quote: |
Temporary Tables: Declared and Created
by Craig S. Mullins
Created Temporary Tables ...
· Created temporary tables can not be specified as the object of an UPDATE statement.
Declared Temporary Tables ....
· You can issue UPDATE statements and positioned DELETE statements against a declared temporary table.
|
Rick |
|
| Back to top |
|
|
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
I have declared the temporary table, only then inserting the records initially and then trying to update the values of those records, but this is not happening.
update is not supported ;-( |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
You folks really need to read thru the info thoroughly ...
Srihari: In the link you provided, look under the column heading Declared temporary tables:
| Quote: |
Indexes and other SQL statement support Indexes and SQL statements that modify data (INSERT, UPDATE, DELETE, and so on) are supported.
|
Rick |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
| Rick, You are right. Excuse me for the oversight..:-) |
|
| Back to top |
|
|
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
| Rick can you suggest what might be the possible flaws or thing I might missed out or need to take care? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Are you sure your update is successful? |
|
| Back to top |
|
|
rpuhlman
New User
Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio
|
|
|
|
Hi Vinay,
Please "Code" and paste any compile/runtime messages you are receiving, along with your SQL and any relevant code. What version of DB2 are you using? Srihari has provided you with a couple of excellent references.
Rick |
|
| Back to top |
|
|
vinay_care
New User
Joined: 22 Sep 2006
Posts: 17
|
|
|
|
Thanks guys, and sorry for delayed response.
Has such I'm creating the temporary table in my application, the update is not supporting, even though sqlcode shows zero after the query is executed, the values does not get updated.
I did change the logic of array usage and only 'insert' on temporary table. this resolved my issue. |
|
| Back to top |
|
|
|
|
