CODEWITHSUNDEEP
sqloracle-databaseinsertoracle11gmerge
Luke
Luke

Reputation: 3

My merge is not correct in Oracle 11G

Can somebody explain to me what I am doing wrong with this statement. Its pretty basic....

merge into week1wrs 
using 
(select wr_id,fname,lname from wrname_id) on 
(week1wrs.fname=wrname_id.fname and week1wrs.lname=wrname_id.LNAME)
when matched then update set week1wrs.WR_ID=wrname_id.wr_id
when not matched update set week1wrs.Name_id='';

* I am using oracle 11g

Upvotes: 0

Views: 68

Answers (2)

radar
radar

Reputation: 13425

use table alias names as Target (T) and Source (S)

merge into week1wrs T
using 
(select wr_id,fname,lname from wrname_id) S on 
(S.fname=T.fname and S.lname=T.LNAME)
when matched then update set T.WR_ID=S.wr_id
when not matched --need to be insert here.

Upvotes: 2

user1897277
user1897277

Reputation: 495

when not matched update set week1wrs.Name_id=''

When not matched you can only INSERT, you cannot update.

Merge syntax:

 MERGE INTO table
 USING table | subquery
 ON condition
 WHEN MATCHED THEN UPDATE SET col = expression | DEFAULT
 where_clause
 DELETE where_clause
 WHEN NOT MATCHED THEN INSERT (col, col2)
 VALUES (expr1, expr2 | DEFAULT)
 where_clause
 WHERE condition;

Upvotes: 1

Related Questions