chalie001
asked on
ORA:30926: unable to get stable set of rows in the source table
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
SELECT *
FROM (
SELECT col1, col2, col3,
count(*) over(partition by col1) cnt
FROM source_table
or this s
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;
how can i do this to above querySELECT *
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
so i can ignore duplicate values
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER