Rectangle 27 3

Merge statement sounds like a plan, except that the trigger won't fire when you're doing the first insert because you've mentioned it's an AFTER UPDATE trigger, not an AFTER INSERT trigger.

SELECT pk FROM orig_tab

Better way would be to define an AFTER INSERT OR UPDATE trigger, combine it with INSERT/UPDATING keywords to handle inserts/updates & use :new/:old to handle new data & old data respectively.

CREATE OR replace TRIGGER vr_reporting_trigger
  AFTER INSERT OR UPDATE ON orig_tab
  FOR EACH ROW
BEGIN
    IF inserting THEN
      INSERT INTO rep_tab
                  (pk,
                   name)
      VALUES      (:NEW.pk,
                   :NEW.name);
    ELSIF updating THEN
      UPDATE rep_tab r
      SET    name = :NEW.name
      WHERE  r.pk = :old.pk;
    END IF;
END vr_reporting_trigger;

Thanks Sathya for your response. A little problem I can see with updating: If a record is updated in orig_tab and no corresponding record in rep_tab then this logic might not work

@Jaanna you can check for the presence of the record & insert it, if it's not present

elsif updating then if rep_tab.pk <> :old.pk then INSERT INTO rep_tab (pk,name) VALUES (:NEW.pk, :NEW.name); else UPDATE rep_tab r SET name = :NEW.name WHERE r.pk = :old.pk; endif; doesn't work

@Jaanna er, you can't refer to a record table just by using tablename.columnname

oracle - PLSQL Trigger to update field value in another table - Stack ...

oracle plsql oracle11g plsqldeveloper
Rectangle 27 3

There are some corner cases that aren't handled in previous answers.

What if a matching pk already exists in the reporting table, when a row is inserted. (We wouldn't normally expect this to happen, but consider what would happen if someone deleted a row from the orig_tab, and then inserted it again. (This is the kind of problem that's going to crop up in production, not in test, at the most inopportune time. Better to plan for it now.)

BEGIN
   IF inserting THEN
      -- insure we avoid duplicate key exception with a NOT EXISTS predicate
      INSERT INTO rep_tab(pk,name)
      SELECT :new.pk, :new.name FROM DUAL
      WHERE NOT EXISTS (SELECT 1 FROM rep_tab WHERE pk = :new.pk);
      -- if row already existed, there's a possibility that name does not match
      UPDATE rep_tab t SET t.name = :new.name 
       WHERE t.pk = :new.pk;
      -- could improve efficiency of update by checking if update is actually
      -- needed using a nullsafe comparison ( t.name <=> :new.name );
   ELSIF updating THEN
      -- handle updates to pk value (note: the row to be updated may not exist
      -- so we need to fallthru to the merge)
      IF :new.pk <> :old.pk THEN
         UPDATE rep_tab t
            SET t.pk = :new.pk
              , t.name = :new.name
          WHERE t.pk = :old.pk ;
      END IF;
      MERGE INTO rep_tab d
      USING DUAL ON (d.pk = :old.pk)
      WHEN MATCHED THEN
      UPDATE SET d.name = :new.name
      WHEN NOT MATCHED THEN
      INSERT (d.pk,d.name) VALUES (:new.pk,:new.name);
   END IF;
END;

oracle - PLSQL Trigger to update field value in another table - Stack ...

oracle plsql oracle11g plsqldeveloper
Rectangle 27 354

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Assuming the join results in a key-preserved view, you could also

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

In your first code example: Is the outer WHERE-clause necessary for correct results? Or do you use it only to speed up the query?

@totoro - In the first example, the WHERE EXISTS prevents you from updating a row in t1 if there is no matching row in t2. Without it, every row in t1 will be updated and the values will be set to NULL if there is no matching row in t2. That is generally not what you want to happen so the WHERE EXISTS is generally needed.

It's worth adding that the SELECT ... FROM t2 must result in a unique row. This means that you have to select on all the fields which comprise a unique key -- a non-unique primary key is not sufficient. Without uniqueness, you are reduced to something like @PaulKarr's loop -- and if there is not a unique correlation, then more than one target row may be updated for each source row.

@RachitSharma - That means that your subquery (the query from table2) is returning multiple rows for one or more table1 values and Oracle doesn't know which one you want to use. Normally, that means that you need to refine the subquery so that it returns a single distinct row.

Oracle SQL: Update a table with data from another table - Stack Overfl...

sql oracle sql-update
Rectangle 27 354

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Assuming the join results in a key-preserved view, you could also

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

In your first code example: Is the outer WHERE-clause necessary for correct results? Or do you use it only to speed up the query?

@totoro - In the first example, the WHERE EXISTS prevents you from updating a row in t1 if there is no matching row in t2. Without it, every row in t1 will be updated and the values will be set to NULL if there is no matching row in t2. That is generally not what you want to happen so the WHERE EXISTS is generally needed.

It's worth adding that the SELECT ... FROM t2 must result in a unique row. This means that you have to select on all the fields which comprise a unique key -- a non-unique primary key is not sufficient. Without uniqueness, you are reduced to something like @PaulKarr's loop -- and if there is not a unique correlation, then more than one target row may be updated for each source row.

@RachitSharma - That means that your subquery (the query from table2) is returning multiple rows for one or more table1 values and Oracle doesn't know which one you want to use. Normally, that means that you need to refine the subquery so that it returns a single distinct row.

Oracle SQL: Update a table with data from another table - Stack Overfl...

sql oracle sql-update
Rectangle 27 2

Summarize all NULLs of a table

In order to count NULL values for all columns of a table T you could run

SELECT COUNT(*) - COUNT(col1) col1_nulls
     , COUNT(*) - COUNT(col2) col2_nulls
     ,..
     , COUNT(*) - COUNT(colN) colN_nulls
     , COUNT(*) total_rows
FROM   T
/

Where col1, col2, .., colN should be replaced with actual names of columns of T table.

Aggregate functions -like COUNT()- ignore NULL values, so COUNT(*) - COUNT(col) will give you how many nulls for each column.

If you want to know how many fields are NULL, I mean every NULL of every record you can

WITH d as (    
    SELECT COUNT(*) - COUNT(col1) col1_nulls
         , COUNT(*) - COUNT(col2) col2_nulls
         ,..
         , COUNT(*) - COUNT(colN) colN_nulls
         , COUNT(*) total_rows
    FROM   T
) SELECT col1_nulls + col1_nulls +..+ colN_null
  FROM d 
/

Following is an improvement in which you need to now nothing but table name and it is very easy to code a function based on it

DECLARE
  T    VARCHAR2(64) := '<YOUR TABLE NAME>';
  expr VARCHAR2(32767);
  q    INTEGER;
BEGIN
  SELECT 'SELECT /*+FULL(T) PARALLEL(T)*/' || COUNT(*) || ' * COUNT(*) OVER () - ' || LISTAGG('COUNT(' || COLUMN_NAME || ')', ' + ') WITHIN GROUP (ORDER BY COLUMN_ID) || ' FROM ' || T
  INTO   expr
  FROM   USER_TAB_COLUMNS
  WHERE  TABLE_NAME = T;

  -- This line is for debugging purposes only
  DBMS_OUTPUT.PUT_LINE(expr);

  EXECUTE IMMEDIATE expr INTO q;

  DBMS_OUTPUT.PUT_LINE(q);
END;
/

Due to calculation implies a full table scan, code produced in expr variable was optimized for parallel running.

CREATE OR REPLACE FUNCTION null_fields(table_name IN VARCHAR2, owner IN VARCHAR2 DEFAULT USER)
  RETURN INTEGER IS
  T    VARCHAR2(64) := UPPER(table_name);
  o    VARCHAR2(64) := UPPER(owner);
  expr VARCHAR2(32767);
  q    INTEGER;
BEGIN
  SELECT 'SELECT /*+FULL(T) PARALLEL(T)*/' || COUNT(*) || ' * COUNT(*) OVER () - ' || listagg('COUNT(' || column_name || ')', ' + ') WITHIN GROUP (ORDER BY column_id) || ' FROM ' || o || '.' || T || ' t'
  INTO   expr
  FROM   all_tab_columns
  WHERE  table_name = T;

  EXECUTE IMMEDIATE expr INTO q;

  RETURN q;
END;
/

-- Usage 1
SELECT null_fields('<your table name>') FROM dual
/

-- Usage 2
SELECT null_fields('<your table name>', '<table owner>') FROM dual
/

sql - Count the number of null values into an Oracle table? - Stack Ov...

sql oracle plsql
Rectangle 27 180

The MERGE statement merges data between two tables. Using DUAL allows us to use this command.

create or replace
procedure ups(xa number)
as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;

A                      B
---------------------- ----------------------
10                     2
20                     1

Apparently the "merge into" statement is not atomic. It can result in "ORA-0001: unique constraint" when used concurrently. The check for existence of a match and the insertion of a new record are not protected by a lock, so there is a race condition. To do this reliably, you need to catch this exception and either re-run the merge or do a simple update instead. In Oracle 10, you can use the "log errors" clause to make it continue with the rest of the rows when an error occurs and log the offending row to another table, rather than just stopping.

Hi, I tried to use same query pattern in my query but somehow my query is inserting duplicate rows. I am not able to find more information about DUAL table. Can anyone please tell me where can I get information of DUAL and also about merge syntax?

@Shekhar Dual is a dummy table with a single row and columnn adp-gmbh.ch/ora/misc/dual.html

@TimSylvester - Oracle uses transactions, so guarantees the snapshot of data at the start of a transaction is consistent throughout the transaction save any changes made within it. Concurrent calls to the database uses the undo stack; so Oracle will manage the final state based on the order of when the concurrent transactions started/completed. So, you'll never have a race condition if a constraint check is done before the insert regardless of how many concurrent calls are made to same SQL code. Worst case, you may get lots of contention and Oracle will take much longer to reach a final state.

@RandyMagruder Is it the case that its 2015 and we still cannot do a upsert reliably in Oracle! Do you know of a concurrent safe solution?

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert
Rectangle 27 180

The MERGE statement merges data between two tables. Using DUAL allows us to use this command.

create or replace
procedure ups(xa number)
as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;

A                      B
---------------------- ----------------------
10                     2
20                     1

Apparently the "merge into" statement is not atomic. It can result in "ORA-0001: unique constraint" when used concurrently. The check for existence of a match and the insertion of a new record are not protected by a lock, so there is a race condition. To do this reliably, you need to catch this exception and either re-run the merge or do a simple update instead. In Oracle 10, you can use the "log errors" clause to make it continue with the rest of the rows when an error occurs and log the offending row to another table, rather than just stopping.

Hi, I tried to use same query pattern in my query but somehow my query is inserting duplicate rows. I am not able to find more information about DUAL table. Can anyone please tell me where can I get information of DUAL and also about merge syntax?

@Shekhar Dual is a dummy table with a single row and columnn adp-gmbh.ch/ora/misc/dual.html

@TimSylvester - Oracle uses transactions, so guarantees the snapshot of data at the start of a transaction is consistent throughout the transaction save any changes made within it. Concurrent calls to the database uses the undo stack; so Oracle will manage the final state based on the order of when the concurrent transactions started/completed. So, you'll never have a race condition if a constraint check is done before the insert regardless of how many concurrent calls are made to same SQL code. Worst case, you may get lots of contention and Oracle will take much longer to reach a final state.

@RandyMagruder Is it the case that its 2015 and we still cannot do a upsert reliably in Oracle! Do you know of a concurrent safe solution?

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert
Rectangle 27 180

The MERGE statement merges data between two tables. Using DUAL allows us to use this command.

create or replace
procedure ups(xa number)
as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;

A                      B
---------------------- ----------------------
10                     2
20                     1

Apparently the "merge into" statement is not atomic. It can result in "ORA-0001: unique constraint" when used concurrently. The check for existence of a match and the insertion of a new record are not protected by a lock, so there is a race condition. To do this reliably, you need to catch this exception and either re-run the merge or do a simple update instead. In Oracle 10, you can use the "log errors" clause to make it continue with the rest of the rows when an error occurs and log the offending row to another table, rather than just stopping.

Hi, I tried to use same query pattern in my query but somehow my query is inserting duplicate rows. I am not able to find more information about DUAL table. Can anyone please tell me where can I get information of DUAL and also about merge syntax?

@Shekhar Dual is a dummy table with a single row and columnn adp-gmbh.ch/ora/misc/dual.html

@TimSylvester - Oracle uses transactions, so guarantees the snapshot of data at the start of a transaction is consistent throughout the transaction save any changes made within it. Concurrent calls to the database uses the undo stack; so Oracle will manage the final state based on the order of when the concurrent transactions started/completed. So, you'll never have a race condition if a constraint check is done before the insert regardless of how many concurrent calls are made to same SQL code. Worst case, you may get lots of contention and Oracle will take much longer to reach a final state.

@RandyMagruder Is it the case that its 2015 and we still cannot do a upsert reliably in Oracle! Do you know of a concurrent safe solution?

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert
Rectangle 27 21

and then insert

insert into new_table ( select * from old_table);

If you want to create table without data . You can use :

create table new_table as ( select * from old_table where 1=0);

The 'insert into' is redundant since the 'create table as' will create table and insert all data.

sql - Oracle copy data to another table - Stack Overflow

sql oracle
Rectangle 27 87

The dual example above which is in PL/SQL was great becuase I wanted to do something similar, but I wanted it client side...so here is the SQL I used to send a similar statement direct from some C#

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name") 
    VALUES ( 2097153,"smith", "john" )

However from a C# perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was.

I've come back here to check out this pattern again. It fails silently when concurrent inserts are attempted. One insert takes effect, the second merge neither inserts or updates. However, the faster approach of doing two separate statements is safe.

oralcle newbies like me may ask what is this dual table see this : stackoverflow.com/q/73751/808698

Too bad that with this pattern we need to write twice the data (John, Smith...). In this case, I win nothing using MERGE, and I prefer using much simpler DELETE then INSERT.

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert
Rectangle 27 86

The dual example above which is in PL/SQL was great becuase I wanted to do something similar, but I wanted it client side...so here is the SQL I used to send a similar statement direct from some C#

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name") 
    VALUES ( 2097153,"smith", "john" )

However from a C# perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was.

I've come back here to check out this pattern again. It fails silently when concurrent inserts are attempted. One insert takes effect, the second merge neither inserts or updates. However, the faster approach of doing two separate statements is safe.

oralcle newbies like me may ask what is this dual table see this : stackoverflow.com/q/73751/808698

Too bad that with this pattern we need to write twice the data (John, Smith...). In this case, I win nothing using MERGE, and I prefer using much simpler DELETE then INSERT.

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert
Rectangle 27 86

The dual example above which is in PL/SQL was great becuase I wanted to do something similar, but I wanted it client side...so here is the SQL I used to send a similar statement direct from some C#

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name") 
    VALUES ( 2097153,"smith", "john" )

However from a C# perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was.

I've come back here to check out this pattern again. It fails silently when concurrent inserts are attempted. One insert takes effect, the second merge neither inserts or updates. However, the faster approach of doing two separate statements is safe.

oralcle newbies like me may ask what is this dual table see this : stackoverflow.com/q/73751/808698

Too bad that with this pattern we need to write twice the data (John, Smith...). In this case, I win nothing using MERGE, and I prefer using much simpler DELETE then INSERT.

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert
Rectangle 27 50

We now finally have IDENTITY columns like many other databases, in case of which a sequence is auto-generated behind the scenes. This solution is much faster than a trigger-based one as can be seen in this blog post.

CREATE TABLE qname
(
    qname_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
    qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
);

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The standard way to have "auto increment" columns in Oracle is to use triggers, e.g.

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT 
  ON qname
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  WHEN (new.qname_id is null)
DECLARE
 v_id qname.qname_id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT qname_id_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.qname_id := v_id;
END my_trigger;

Read more about Oracle TRIGGERs in the documentation

Thank you very much! I am a newbie. May i know why you have used the line, :new.id := v_id; ? Pls help!

I'll update the answer. Note, I had a typo. It should be :new.qname_id, not :new.id

Now, I am pretty clear about this. Thanks man!

+1 for the trigger method, but i think you could lose the v_id variable completely and just select into the :new.qname_id. You might also just fire the trigger when new.qname_id is null as it allows code to reference the sequence nextval directly in the insert and bypass trigger execution.

@LukasEder, I don't think that the null check is a good idea- lets say that nextval=10 and someone inserted a record with qname_id=10000, everything will be fine! untill one day (perhaps a year later) nextval will reach 10000 ...

sql - create table with sequence.nextval in oracle - Stack Overflow

sql database oracle plsql
Rectangle 27 50

We now finally have IDENTITY columns like many other databases, in case of which a sequence is auto-generated behind the scenes. This solution is much faster than a trigger-based one as can be seen in this blog post.

CREATE TABLE qname
(
    qname_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
    qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
);

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The standard way to have "auto increment" columns in Oracle is to use triggers, e.g.

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT 
  ON qname
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  WHEN (new.qname_id is null)
DECLARE
 v_id qname.qname_id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT qname_id_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.qname_id := v_id;
END my_trigger;

Read more about Oracle TRIGGERs in the documentation

Thank you very much! I am a newbie. May i know why you have used the line, :new.id := v_id; ? Pls help!

I'll update the answer. Note, I had a typo. It should be :new.qname_id, not :new.id

Now, I am pretty clear about this. Thanks man!

+1 for the trigger method, but i think you could lose the v_id variable completely and just select into the :new.qname_id. You might also just fire the trigger when new.qname_id is null as it allows code to reference the sequence nextval directly in the insert and bypass trigger execution.

@LukasEder, I don't think that the null check is a good idea- lets say that nextval=10 and someone inserted a record with qname_id=10000, everything will be fine! untill one day (perhaps a year later) nextval will reach 10000 ...

sql - create table with sequence.nextval in oracle - Stack Overflow

sql database oracle plsql
Rectangle 27 44

In Oracle 12c, you can now specify the CURRVAL and NEXTVAL sequence pseudocolumns as default values for a column. Alternatively, you can use Identity columns; see:

CREATE SEQUENCE t1_seq;
CREATE TABLE t1 (
  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)
);

All I can say is, "It's about time!"

sql - create table with sequence.nextval in oracle - Stack Overflow

sql database oracle plsql
Rectangle 27 44

In Oracle 12c, you can now specify the CURRVAL and NEXTVAL sequence pseudocolumns as default values for a column. Alternatively, you can use Identity columns; see:

CREATE SEQUENCE t1_seq;
CREATE TABLE t1 (
  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)
);

All I can say is, "It's about time!"

sql - create table with sequence.nextval in oracle - Stack Overflow

sql database oracle plsql
Rectangle 27 29

MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;

Oracle SQL: Update a table with data from another table - Stack Overfl...

sql oracle sql-update
Rectangle 27 28

MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;

Oracle SQL: Update a table with data from another table - Stack Overfl...

sql oracle sql-update
Rectangle 27 4

Mutating table errors almost always indicate a problem with the data model or related business processes. The most common cause is denormalisation, that is where data in one table is duplicated in some fashion in another table. That seems to be the case here - your CUSTOMER table is holding metadata about another table, PERSON. Only it's compounded by the cascade of information in the other direction.

The proper way to resolve this situation is to sort out the data model. Is CUSTOMER a sub-type of PERSON or is it the other way round? Determine which is the parent and which is the child, and make sure that information only flows in one direction: probably from super-type to sub-type. Although a better solution would be to remove the data propagation altogether.

There are workarounds but they involve packages and other contrivances to apply changes.

oracle - Clashing triggers - mutating table - Stack Overflow

oracle plsql triggers mutating-table
Rectangle 27 42

Another alternative without the exception check:

UPDATE tablename
    SET val1 = in_val1,
        val2 = in_val2
    WHERE val3 = in_val3;

IF ( sql%rowcount = 0 )
    THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

Your provided solution does not work for me. Does %rowcount only work with explicit cursors?

What if the update returned 0 rows modified because the record was already there and the values were the same?

@Adriano: sql%rowcount will still return > 0 if the WHERE clause matches any rows, even if the update doesn't actually change any data on those rows.

@Tony Andrews - is this solution thread safe?

Doesn't work: PLS-00207: identifier 'COUNT', applied to implicit cursor SQL, is not a legal cursor attribute

sql - Oracle: how to UPSERT (update or insert into a table?) - Stack O...

sql oracle merge upsert