RE: MERGE
- From: <genegurevich@xxxxxxxxxxxx>
- To: "Ken Naim" <kennaim@xxxxxxxxx>
- Date: Tue, 22 Jul 2008 17:05:04 -0500
Thanks, Ken
Thats a very good option
thank you
Gene Gurevich
"Ken Naim"
<kennaim@xxxxxxxx
m> To
<genegurevich@xxxxxxxxxxxx>,
07/22/2008 04:48 <oracle-l@xxxxxxxxxxxxx>
PM cc
Subject
RE: MERGE
Just use case statements in your merge command to determine if the value in
the permanent and staging are defaults and adjust your logic accordingly.
You can also use a where clause to filter out cases of all defaults, or to
make sure atleast one non default exists.
If this is a high volume application, setting many columns to themselves in
the case of non defaults can add a lot of load in which case the separate
statements could perform better. This will depend on the ratio of defaults
to non defaults, and whether indexes are present and enabled on these
columns. Testing is key.
Merge into ...
Using ...
On ...
When matched
Then update
set x.coll2= case when x.col12=0 then y.coll12 else coll12
Ken Naim
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of genegurevich@xxxxxxxxxxxx
Sent: Tuesday, July 22, 2008 4:12 PM
To: "oracle-l@xxxxxxxxxxxxx"@discover.com
Subject: MERGE
Hi all:
My application team is doing the following. They have a table (col01,
col02, col11, col12, col13) where col01 and
col02 are primary key. They want to use the merge command to update/insert
the data from a staging table which
has the same layout. One of the values in the col11/12/13 in the staging
table is the new data, the other one or two
are some default values. When they do merge command, these default data
overwrite the data in the main table.
They are looking for a way to only modify the data in one of the columns
(11/12/13) but not the others. If the staging
table's col11 has the real data and col12 and col13 have default values,
they only want to modify the col11 table
in the main table. If the columns col11 and col13 in the stage table have
the real data, they only want to modify
these columns in the main table without affecting the col12 etc. I don't
think that one MERGE command will do that
and that they would either need to provide all the correct data (no
defaults) in the staging table or to create different versions of merge
commands (updating different columns in the main table) and call them based
on what data are written to the staging table.
Am I correct? Is there another (better) way to accomplish this?
thank you
Gene Gurevich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
