Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle update

Posted on 2014-09-10
4
Medium Priority
?
233 Views
Last Modified: 2014-09-14
Hi,
The following query is not behaving as I would expect:

UPDATE DWH.DIM_AGENT d
SET (
FIRST_NAME
) = (
select
ah.FIRST_NAME
from STG.AGENT_HIERARCHY ah
WHERE
ah.A_ID = d.A_ID
AND d.IS_CURRENT = 1
and ah.FIRST_NAME != d.FIRST_NAME
);

Dim_Agent is a dimension in a data warehouse and I would like to update first name only if the incoming first name is different.  The query as it is sets first name on the dimension to null for all rows.  If I comment out the not equal portion of the where clause, it updates all rows in the dimension as I would expect, but I would like to only update rows which have changed.
Thanks,
Greg
0
Comment
Question by:Greg_Beam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 40315201
I would expect all first names that are equal become null , unless d,is_current does not equal 1 , but the changed names should be changed.
To filter the rows you will have to move the condition to the where of the update and not the where to get the new value.

UPDATE DWH.DIM_AGENT d
SET (
FIRST_NAME
) = (
select
ah.FIRST_NAME
from STG.AGENT_HIERARCHY ah
WHERE
ah.A_ID = d.A_ID
)
where (
select
ah.FIRST_NAME
from STG.AGENT_HIERARCHY ah
WHERE
ah.A_ID = d.A_ID
) != d.FIRST_NAME
and d.IS_CURRENT = 1;
0
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 total points
ID: 40315240
or use a merge to select once (could not check the syntax)

MERGE INTO DWH.DIM_AGENT md
    USING (select ah.a_id , ah.FIRST_NAME
                 from STG.AGENT_HIERARCHY ah,
                 DWH.DIM_AGENT d
                 WHERE ah.A_ID = d.A_ID
                 AND d.IS_CURRENT = 1
                 and ah.FIRST_NAME != d.FIRST_NAME
                 ) mq
    ON (mq.a_id = md.a_id)
  WHEN MATCHED THEN
    UPDATE SET d.first_name = mq.address
  -- WHEN NOT MATCHED THEN
  -- INSERT (xxx)
 --   VALUES (xx, x);
  ;
0
 

Author Comment

by:Greg_Beam
ID: 40315425
There are actually many columns to check for changes.  I think the merge would handle multiple columns more easily, is that right?  for the merge, the insert part is not needed as the criteria for insert are different and I was planning to do that separtely.  For the merge, I think I would or togehter the check fore the changes in the remaining columns, is that right?
Thanks
0
 
LVL 20

Expert Comment

by:flow01
ID: 40315789
Yes, i agree : the merge method it is more easily if you have multiple columns.
The insert part i only left  to show why it's called a merge and what you can do with it.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question