Merge into same table itself
587785Sep 11 2007 — edited Sep 12 2007Hi,
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.