Re: UPDATE SET of multiple columns

On Thu, 13 Apr 2006 22:19:16 GMT, Brian Peasland <oracle_dba_at_nospam.peasland.net> wrote:

>> Thank you for your reply.
>>
>> I already have 6 update statements; but I would like to consolidate
>> them into one to reduce the runtime of a job.
>
>I like the solution proposed by Kenneth....very elegant!
>

It works, but it updates every column in every row in the table...not fine it it has got 100M rows. And all columns not being null get updated to exactly the same value as before ---> lot of I/O generated accomplishing nothing.

The OP asked for a single statement, which he got.

But it is not elegant at all...it is in fact quite dumb, IMHO, and I would always prefer the 6-step thing.

Maybe the optimizer in a later version of Oracle wil be able to recognize statements as

update mytable
set c1 = c1

in fact do nothing and thus optimize it to do nothing with c1. Maybe in 11h ?

• Kenneth Koenraadt