Re: Problem while inserting a record into a table

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Tue, 7 Sep 2010 06:18:37 -0700 (PDT)
Message-ID: <666d2c8f-6240-4e88-b3a6-cfc0a5aeb098_at_b34g2000yqm.googlegroups.com>

On Sep 7, 7:44 am, Sandy80 <svarshneym..._at_gmail.com> wrote:
> hi,
>
> I am trying to insert a record in a employee table 1 based on whether
> a record already exists in table 1. If a record for that employee
> already exists in table 1, I need to insert the record into table 2
> else into table 1 itself. I am trying to do it through a pl/sql db
> procedure.
> So the code I have for insert statement is as follows:
>
> insert when (empno = 1234)
>         then into table 2 (empname,empno)
>         values (
>         AAAA,
>         1234
>         )
>         else
>         into table 1(empname,empno)
>         values (
>         AAAA,
>         1234
>         )
>         select * from table 1 where empno = 1234;
>
> The insert statement works when there are no records in table 1 for
> that employee but fails when there are no records. Can anyone help me
> as what do I need to add/change in this query for it to work in both
> the conditions. Any help would be appreciated!

Sandy80,
I may not understand your requirement clearly, but look at MERGE statement:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606 HTH
Thomas Received on Tue Sep 07 2010 - 08:18:37 CDT