Oracle update if exist or insert using merge dual on single table

Question

Here is the table Student:

Name	Surname	Age	Passed
John	Wick	20	N
Alice	Wonderland	21	Y

I want to update if students exist on table based on age, name, surname or insert as a new row.

here is the sql query:

merge into student t
using (
    select
        ? AS NAME,
        ? AS SURNAME,
        ? AS AGE,
        ? AS PASSED
    from
        dual
) s on (
    t.NAME=s.NAME
    and t.SURNAME=s.SURNAME
    and t.AGE=s.AGE
    and t.PASSED=s.PASSED
)
when matched then 
    update set t.PASSED = s.PASSED 
when not matched then 
    insert (NAME, SURNAME, AGE, PASSED) values (s.NAME, s.SURNAME, s.AGE, s.PASSED);

Here is the jdbcTemplate batchUpdate method impl:

jdbcTemplate.batchUpdate(query, new BatchPreparedStatementSetter(){

   @Override
   public void setValues(PreparedStatment ps, int i){
     Student s = studentList.get(i);

       ps.setString(1, s.getName());
       ps.setString(2, s.getSurname());
       ps.setInt(3, s.getAge());
       ps.setString(4, s.getPassed());

     }

    @Override
       public int getBatchSize(){
       return studentList.size()
    }

});

ORA-38104: Columns referenced in the ON Clause cannot be updated

thanks in advance.

Answer 1

Don't include the column you are updating in the ON clause.

merge into student t
using (
    select
        ? AS NAME,
        ? AS SURNAME,
        ? AS AGE,
        ? AS PASSED
    from
        dual
) s on (
    t.NAME=s.NAME
    and t.SURNAME=s.SURNAME
    and t.AGE=s.AGE
    -- and t.PASSED=s.PASSED                      -- Remove this filter.
)
when matched then 
    update set t.PASSED = s.PASSED 
when not matched then 
    insert (NAME, SURNAME, AGE, PASSED)
    values (s.NAME, s.SURNAME, s.AGE, s.PASSED);

Additionally, you should consider:

• Using a primary key for the table (such as an IDENTITY column) and then you can update based on the primary key rather than a composite key (as it is possible to have two distinct students with the same names and age and you need to be able to distinguish between them);
• Storing date of birth, rather than age (as age changes with each person's birthday whereas date of birth is constant and can be used to calculate the age).