0
MERGE INTO tbl_brk_sch_cat_mappin_temp bsc
USING  lookup l
ON (l.lookup_id = bsc.catery_id) 
WHEN MATCHED THEN
UPDATE SET bsc.new_category=l.longname where  bsc.created_date=to_date('10/30/2015','MM/DD/YYYY');

I've ran tbl_brk_sch_cat_mappin_temp it has data and also I've ran the 'lookup' table which also has data.

Why would this error come and how can it be resolved?

Shadow
  • 33,525
  • 10
  • 51
  • 64
user3682373
  • 61
  • 8

1 Answers1

1

This issue occurs when you have more than one row in the source data (in your case, the lookup table) that matches against a single row in your target table (in your case, the tbl_brk_sch_cat_mappin_temp table) based on your join condition.

In this scenario, you're asking Oracle to update a single row with two different values, which doesn't really make sense to do.

To fix the issue, you need to either correct the data in your source table or fix the join condition so that each row in your target table matches against a single row in your source table.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • we are updating a single row with single value at a time according to our join condition. first it checks if the join condition match and then it updates. previously it used to work. @Boneist – user3682373 Nov 02 '15 at 10:55
  • so what does the following query return? `select bsc.catery_id, count(*) from tbl_brk_sch_cat_mappin_temp bsc inner join lookup l on (l.lookup_id = bsc.catery_id) group by bsc.catery_id having count(*) > 1;` – Boneist Nov 02 '15 at 11:02
  • we get 41 rows with multiple counts @boneist – user3682373 Nov 02 '15 at 11:09
  • So that's your problem then. If, for example, `bsc.catery_id = 1` matches with two rows where `l.lookup_id = 1`, one of which has `l.longname = 'name1'` and the other has `l.longname = 'name2'`, what is bsc.new_category meant to be set to? name1 or name2? It clearly can't be set to be both at the same time! So, you need to make sure that the source dataset only returns one row per join condition (i.e. in your case, you need to make sure that lookup_id is unique in the source dataset. Maybe you need an extra predicate in your join condition, or maybe you need to do an aggregate/distinct) – Boneist Nov 02 '15 at 11:15
  • Thank You so much it worked for me there was a duplicate entry we got it corrected @Boneist – user3682373 Nov 02 '15 at 12:18
  • I'm glad you managed to get it sorted. If the table isn't supposed to have duplicate entries, you should consider adding in a unique constraint to prevent the issue from occurring again. – Boneist Nov 02 '15 at 12:26