Solved

ORA:30926: unable to get stable set of rows in the source table

Posted on 2015-01-25
2
340 Views
Last Modified: 2015-01-29
hi i have the frollowing merg sql how can i avoid the duplicate value from source table to update the target table,or is the another way i can work around this
merge into tmp_cal_obj e1
          using cal_book e2
          on (e1.obj_name = e2.obj_name)
          when matched then update set e1.description = e2.description;

Open in new window

how can i do this to above query
SELECT *
FROM (
SELECT col1, col2, col3,
count(*) over(partition by col1) cnt
FROM source_table

or this s
merge into cms_equipment eq
     using (select *
              from (select row_number () over (partition by trim (stg.equipment_id), vpf.vpf_company_nr order by null) rn,
                           vpf.vpf_company_nr,
                           trim (stg.equipment_id) as equipment_id,
                           stg.equipment_type,
                           nvl (stg.installation_date, stg.effective_date) as installation_date,
                           stg.projector_model,
                           stg.ti_software,
                           stg.projector_serial,
                           stg.server_software_version,
                           stg.certificate_number,
                           stg.mb_firmware_version,
                           stg.mb_link_encryption,
                           stg.watermarking,
                           stg.system3d,
                           stg.server_model,
                           stg.server_serial
                      from cms_equip_staging stg, cms_vpf_company vpf
                     where upper (trim (stg.vpf_company_name)) = upper (trim (vpf.vpf_company_name)) and upper (trim (stg.data_load_status)) = 'NEW')
             where rn = 1) estg
        on (upper (trim (estg.equipment_id)) = upper (trim (eq.equipment_id)) and eq.vpf_company_nr = estg.vpf_company_nr)
when matched
then
)
WHERE cnt = 1

Open in new window

so i can ignore duplicate values
0
Comment
Question by:chalie001
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40570416
What description from cal_book should be the 'correct' one?

The one using row_number() is the approach I would take.  

I assume you copied it and don't know how to rewrite it to use your tables?

Try this:
merge into tmp_cal_obj e1
          using (
          	select obj_name, description from
          	(
          		select obj_name, description, row_number() over(partition by obj_name order by description) rn from cal_book
          	)
          	where rn=1
) e2
          on (e1.obj_name = e2.obj_name)
          when matched then update set e1.description = e2.description;

Open in new window


You still need to decide on which description is the correct one.
0
 

Author Closing Comment

by:chalie001
ID: 40578134
thanks
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

Suggested Solutions

Title # Comments Views Activity
unable to get sorting resultset 15 72
PL/SQL Search for multiple strings 5 52
Oracle Public Synonyms and Privileges 2 65
oracle RMAN - trying to duplicate a database 5 28
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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
This video shows how to recover a database from a user managed backup

813 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

14 Experts available now in Live!

Get 1:1 Help Now