I have a merge statement, which inserts or updates data into table sit_conflicts. And i have used all primary key columns in "ON clause" of a merge statement. Here is the merge statement...
MERGE
INTO sit_conflict sc
USING (SELECT conflict_itemloc_link_id,
item,
location,
itemloc_link_id,
insert_date
FROM TABLE(L_sit)) sct1
ON (sc.item = sct1.item
AND sc.location = sct1.location
AND sc.conflict_itemloc_link_id = sct1.conflict_itemloc_link_id
AND sc.itemloc_link_id = sct1.itemloc_link_id)
WHEN matched THEN
UPDATE SET sc.insert_date = sct1.insert_date
WHEN NOT matched THEN
INSERT (sc.conflict_itemloc_link_id,
sc.item,
sc.location,
sc.itemloc_link_id,
sc.insert_date)
VALUES (sct1.conflict_itemloc_link_id,
sct1.item,
sct1.location,
sct1.itemloc_link_id,
sct1.insert_date);
where L_sit is a PL/SQL table. If L_sit is null then i should get an error 'Can't insert null into <primary key columns>'. But user is failing with primary key violation?
Does merge statement iterates for each reocord? If so why merge is trying to insert non-unique record?
Regards
Chaitanya