On 12 Apr, 00:57, "shorti" <lbrya...@juno.com> wrote:
> DB2 V8.2 on AIX
>
> I am looking for an efficient way to update several columns in a table
> that will have a default change. The problem is the table is large
> (million records) and there are 1 to 4 columns that might need to be
> changed per record. I wanted to avoid looping through the table 4
> times in order to change them.
>
> Here is an example. Basically, the four columns in question were
> originally defaulted to NULL. Due to changes elsewhere it can no
> longer be NULL so it will be changed to blanks. Here is what the
> table would look like prior to changing the default:
>
> table1
> col1 bigint
> col2 char(8) default null
> col3 char(8) default null
> col4 char(8) default null
> col5 char(8) default null
> col6 smallint default 0
>
> So now there are records in the table where some of the values in col2
> thru col5 are still NULL and some contain values.
>
> I want to parse the table and change anything that is null in these
> four columns to blanks (' '). I am hoping to do with without
> having to loop through it four times because I dont know if all
> columns are null or maybe only one or two.
>
> Anyone have a good query to do this?
>
> Much Appreciated.

Could you not just do a export (with a case statement for the relevant
4 columns) and then an import from <file>.del of del commitcount 10000
insert_update into <table>.
It might be even quicker, if you were to truncate the table after
you've done the export and then do a load...nonrecoverable.