Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Merge into same table itself

587785Sep 11 2007 — edited Sep 12 2007
Hi,
I have a table like this:
id | Year | Ver | Col1
1 |2006 | 1 | 0

My procedure is getting the parameters for Id, Ver, Year, Col1 with values : id=2
year=2006 ver=2 col1=2 Then I need to insert new value. If the record in table1 exists then I need to update otherwise I need to insert. So I am writing like this:
Merge Into Table1 t
using (select year,id,ver from table1) tc
On (tc.year=t.year and t.ver=tc.ver and t.id=tc.id)
When Not macthed insert
When matched update.
Here the problem is if the record exist with the passed parameters in Table1 the n it is ok it will do update, If record not exist then insert but It is giving "No data found" exception.

Thanks in advance
Can we use Merge for this purpose.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 10 2007
Added on Sep 11 2007
11 comments
14,812 views