Don't know if it's the most efficient way, but you can use a case
expression that returns the blanks when the column is null, else the
column value itself, then use that to unconditionally update all rows.
Something like (not tested):
update table1 set c2=(case when c2 is null then ' ' else c2 end),
c3=(case when c3 is null then ' ' else c3 end), c4=(case when c4 is
null then ' ' else c4 end), c5=(case when c5 is null then ' '
else c5 end)
You might want to chunk up the update if the table is huge and your
active log space is limited. Hope this helps.
Regards,
Miro
On Apr 11, 7:57 pm, "shorti" <lbrya...@juno.comwrote:
Quote:
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.
