Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-25
2
Medium Priority
?
370 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

610 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