SQL & PL/SQL

Hi,

For an update, the following MERGE statement throws the error-unable to get a stable set of rows in the source tables:

MERGE INTO table2t INT
USING (SELECT DISTINCT NULL bdl_inst_id,.......
........
........
FROM table1 ftp
WHERE ftp.gld_business_date = g_business_date
AND ftp.underlying_instrument_id IS NOT NULL) ui
ON ( ( INT.inst_id = ui.inst_id
AND g_business_date BETWEEN INT.valid_from_date
AND INT.valid_to_date
)
OR ( INT.ric = ui.ric
AND g_business_date BETWEEN INT.valid_from_date
AND INT.valid_to_date
)
OR ( INT.isin = ui.isin
AND g_business_date BETWEEN INT.valid_from_date
AND INT.valid_to_date
)
OR ( INT.sedol = ui.sedol
AND g_business_date BETWEEN INT.valid_from_date
AND INT.valid_to_date
)
OR ( INT.cusip = ui.cusip
AND g_business_date BETWEEN INT.valid_from_date
AND INT.valid_to_date
))
WHEN MATCHED THEN
UPDATE
SET INT.inst_id = ui.inst_id, INT.ric = ui.ric
WHEN NOT MATCHED THEN
INSERT (inst_key, ......)
VALUES (inst_key, ......);

To determine the existence of a record, first check if any match is found on the first key, if not then search for the second key and so on.

Now two records with differenct first key, i.e. inst_id, can have the same ric(second key). On a rerun, with the target table already populated, the code fails. The reason is it finds duplicate entries for the second key.

Any suggestions on how to make this work?

Thanks in advance.

Annie