Solved

oracle update

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

776 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