Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

571 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