CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-updatesql-insert
Rocky4Ever
Rocky4Ever

Reputation: 868

Oracle: Insert if not exist or Update not working

I am trying to insert if the record not exist otherwise update for the following table.However it is saying 0 0 rows inserted.But when I test my select statement it is returning 1 .

create table tb_coba2 (id number , nis number , nilai_b number , semester number);

Query :

insert into TB_COBA2 (nis , nilai_b , semester)
     select :nis , :nilai_a , :semester
      from dual
      where not exists (
       select 1 from tb_coba2
      where nis = :nis and semester = :semester
      );
      commit;


alter table tb_coba2 add constraint tb_coba2_uq unique ( nis, semester );

The following query returns 1 after first insert but if i run full insert it says 0 records inserted.

select 1 from tb_coba2 where nis = :nis and semester = :semester

What am i doing wrong here.?

Upvotes: 0

Views: 184

Answers (1)

GMB
GMB

Reputation: 222432

Your original code does what it is meant to, that is: insert the row if the given (nis, semester) do not yet exist in the table.

However, your question mentions that you want to update as well, when a record already exists:

I am trying to insert if the record not exist otherwise update

In Oracle, you would use a merge statement for this. Based on your description of the problem, that would be:

merge into tb_coba2 t
using (select :nis nis, :nilai_a nilai_a, :semester semester from dual) s
on (s.nis = t.nis and s.semester = t.semester)
when matched then update set t.nilai_a = s.nilai_a
when not matched then insert (nis, nilai_a, semester) values (s.nis, s.nilai_a, s.semester)

Upvotes: 1

Related Questions