FAQ
=20

I'm writing a batch update/insert procedure and am wondering if there is
a way to only update columns corresponding to the 'on' clause if they
are different from the current value?...instead of update all columns
regardles of whether or not that are the same.

So...

Table stage
Pk_id

Col_a
Col_b
Col_c

Table prod
Pk_id

Col_a
Col_b
Col_c

Merge into a.prod using select * from b.stage on (a.pk_id=3Db.pk_id) =
When
matched then set a.col_a=3Db.col_a (but only if col_a has a different
value) When not matched then insert...;

Thanks
chris

Search Discussions

  • Edgar Chupit at Nov 1, 2004 at 3:51 pm
    Dear Chris,

    It is possible to include where clause in 10g to filter rows in when
    matched subclause.
    From the documentation:
    Specify the where_clause if you want the database to execute the
    update operation only if the specified condition is true. The
    condition can refer to either the data source or the target table. If
    the condition is not true, then the database skips the update
    operation when merging the row into the table.

    On Thu, 28 Oct 2004 14:42:59 -0400, Stephens, Chris
    wrote:
    =20

    I'm writing a batch update/insert procedure and am wondering if there is
    a way to only update columns corresponding to the 'on' clause if they
    are different from the current value?...instead of update all columns
    regardles of whether or not that are the same.

    So...

    Table stage
    Pk_id
    Col_a
    Col_b
    Col_c

    Table prod
    Pk_id
    Col_a
    Col_b
    Col_c

    Merge into a.prod using select * from b.stage on (a.pk_id=3Db.pk_id) =
    When
    matched then set a.col_a=3Db.col_a (but only if col_a has a different
    value) When not matched then insert...;

    Thanks
    chris
    --
    http://www.freelists.org/webpage/oracle-l
    --
    Edgar
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 28, '04 at 1:38p
activeNov 1, '04 at 3:51p
posts2
users2
websiteoracle.com

2 users in discussion

Edgar Chupit: 1 post Stephens, Chris: 1 post

People

Translate

site design / logo © 2022 Grokbase