View previous topic :: View next topic |
Author |
Message |
Nirav721 Beginner
Joined: 07 Feb 2008 Posts: 4 Topics: 1
|
Posted: Sun Feb 10, 2008 3:30 am Post subject: Updating all columns in a table for a row |
|
|
I am wondering if there is a way to update all columns for a particular row excluding the key without having to actually specify all column names within the SET clause? The data would be coming in from a flat file into cobol db2 program...I would like to be able to keep performance in perspective too...
Or, is it better to do delete/insert vs. updating all columns for a particular row within a table?
Any advise would be great!
Thanks |
|
Back to top |
|
 |
vkphani Intermediate

Joined: 05 Sep 2003 Posts: 483 Topics: 48
|
Posted: Mon Feb 11, 2008 4:08 am Post subject: |
|
|
Nirav721,
Try the below SQL.
Code: |
UPDATE TABLE_NAME
SET COLUMN1 = VALUE1,
COLUMN2 = VALUE2,
COLUMN3 = VALUE3,
COLUMN4 = VALUE4,
COLUMN5 = VALUE5
WHERE COLUMN6 = VALUE6
|
|
|
Back to top |
|
 |
Nirav721 Beginner
Joined: 07 Feb 2008 Posts: 4 Topics: 1
|
Posted: Mon Feb 11, 2008 5:02 am Post subject: |
|
|
vkphani,
Thanks for the reply. But, I am looking for a way to avoid writing out the column names since we have some tables with upto 300 columns.....? Would you know anyway to do that?
Thanks |
|
Back to top |
|
 |
CZerfas Intermediate
Joined: 31 Jan 2003 Posts: 211 Topics: 8
|
Posted: Mon Feb 11, 2008 5:19 am Post subject: |
|
|
The UPDATE-Statement forces the use of all the column namnes. The good news is, that you can generate this column list using BMC catalog manager or QMF (look into the manuals for the DRAW commmand).
regards
Christian |
|
Back to top |
|
 |
Nirav721 Beginner
Joined: 07 Feb 2008 Posts: 4 Topics: 1
|
Posted: Mon Feb 11, 2008 6:11 am Post subject: |
|
|
Thanks Chris. I will look into it. I am curious though...not that we would ever do something like this because of a huge performance hit, but would it be possible to declare temporary table, in which we insert into data being read (i m hoping we can just specify space without actually getting into gritty details about each field name....). And then having update table set row = (select * from declaredtab where key = key) ??
Just curious on this part..if it is doable or not?
Thanks! |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
Posted: Mon Feb 11, 2008 11:01 am Post subject: |
|
|
Nirav721,
Why not simply Load the table with the flat file data? You can either do a LOAD REPLACE or LOAD RESUME.
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
 |
Nirav721 Beginner
Joined: 07 Feb 2008 Posts: 4 Topics: 1
|
Posted: Mon Feb 11, 2008 12:59 pm Post subject: |
|
|
Hi,
The reason why we do not want to load replace is because this module is part of batch process that will be called on per-transaction basis...This module will just get the address of the place where data is...we would like the updates to take place as soon as transaction processes rather than waiting until everything processes and then applying the changes to database....
Thanks |
|
Back to top |
|
 |
|
|