Solved

oracle update

Posted on 2014-09-10
4
224 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
  • 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now