Solved

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

Posted on 2015-01-25
2
337 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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows how to recover a database from a user managed backup

863 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

28 Experts available now in Live!

Get 1:1 Help Now