[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

oracle update

Posted on 2014-09-10
4
Medium Priority
?
235 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 21

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 21

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 21

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

591 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