Search code examples
plsqlmergeoledboracle9i

Conditional INSERT / UPDATE in Oracle 9i (PL / SQL)

I am trying to build a query to INSERT or UPDATE / DELETE a row depending on some conditions. I was trying to use the MERGE clause but it has some restrictions that doesn't let me change some fields.

Here is the code:

MERGE INTO CADUSUNET t

USING (select 'FELIPE' as nomusunet from cademp where rownum = 1) v --generate the column and the value to compare
  ON (t.nomusunet = v.nomusunet)

WHEN MATCHED THEN
  UPDATE SET t.nomusunet = 'FELIPE BUENO' --I can't update a column that is referenced in the ON condition clause

WHEN NOT MATCHED THEN
  INSERT (nomusunet) VALUES ('FELIPE BUENO')

Is there a way to do that?

Solution

  • You could do this:

    begin
 update CADUSUNET t
 set t.nomusunet = 'FELIPE BUENO'
 where t.nomusunet = 'FELIPE';

 if sql%rowcount = 0 then
   INSERT INTO CADUSUNET (nomusunet) VALUES ('FELIPE BUENO');
 end if;
end;