?
Solved

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

Posted on 2015-01-25
2
Medium Priority
?
363 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
[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
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

801 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