where L_sit is a PL/SQL table. If L_sit is null then i should get an error 'Can't insert null into <primary key columns>'. But user is failing with primary key violation?probably you mean Nested Table...
SQL> create table t (id number, name varchar2(10)); Table created. SQL> SQL> create type numlist is table of number 2 / Type created. SQL> SQL> SQL> merge into t 2 using (select column_value id 3 , 'thing' name 4 from table (numlist (1,2,3,4,5)) 5 where 1=0 6 ) sctl 7 on (sctl.id = t.id) 8 when matched then update set name = upper (name) 9 when not matched then insert values (sctl.id, sctl.name) 10 / 0 rows merged. SQL> SQL> SQL> select * 2 from t 3 / no rows selected SQL>
It's a single statement, which is validated at the end of the statement.
insert all into t values (id, name) select 1 id, 'test' name from dual union all select 1, 'test' from dual
user773320 wrote:No, the duplicates are in the source.. The MATCHED/NOT MATCHED is done on the target. Look at it this way: it is like inserting two similar records at the same time. Take another look at the INSERT ALL example I gave before.
Duplicates should fall under match condition.. why it's going to insert block (not matched). Merge is update if matched or insert if unmatched. In may case first record (1) is inserted.. the second record should go to Update right?