Hi,
Where condition is not working in Merge Update statement. Sample code is below.
I am trying to update the records with status = 'A' not equal to status with 'M'
It is updating all the records with status 'A' when I remove the Where condition. not sure why its not accepting Where condition. ( I tried with different operators , <>, !=, NOT IN ..etc )
Could someone help me with this?
Kindly assist.
PROCEDURE ProcedureName (pi_setid IN VARCHAR2)
is
begin
v_setid := pi_setid;
if (check_setid (pi_setid)) -- check if the setid is valid
then
merge into X.lec target
using (select pi_setid as setid,
upi_unicode_pkg.to_ascii (l.lecid)
as lecturerid,
upi_unicode_pkg.to_ascii (l.name) as name,
upi_unicode_pkg.to_ascii (l.lcode) as linkcode,
upi_unicode_pkg.to_ascii (l.owner) as owner,
upi_unicode_pkg.to_ascii (l.deid)
as displectid
from lec_view l
where (l.i_current = 'C' AND l.owner IS NOT NULL)) source
on ( (target.lecid = source.lecid)
AND (target.setid = source.setid))
when MATCHED THEN
update set
target.name = source.name,
target.owner = source.owner,
target.linkcode = source.linkcode,
target.status='A' WHERE (status <> 'M')
when not matched
then
insert (......,
target.status)
values (......,
'A');
COMMIT;