Solved

oracle update

Posted on 2014-09-10
4
231 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

729 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