Re: conditional insert with values; insert into .. values(...) where not exist (....);

On Apr 29, 11:08 am, md <mardahl2..._at_yahoo.com> wrote:
> This is what I wish I could to do.
>
> insert into table_x (a, b, c)
> values(1, 2, 3)
> where not exist (select * from table_x where a = 2);

(BTW it helps to spell EXISTS correctly. Best is to copy and paste the query you tried instead of retyping it because something always goes wrong when manually retyping.)
>
> Thank you.
>

you are just so close to seeing the answer, try this: drop table ejp;

create table ejp as
 select 3 a,2 b,1 c from dual ;

select * from ejp ;

 insert into ejp (a, b, c)

           select 2, 2, 3   from dual
               where not exists
                (select * from ejp where a = 2);

select * from ejp ;

 insert into ejp (a, b, c)

           select 1, 2, 3   from dual
               where not exists
                (select * from ejp where a = 2);

select * from ejp ;

And the results:
Table dropped.
Table created.

         A B C
---------- ---------- ----------

         3 2 1

1 row selected.
1 row created.

         A B C
---------- ---------- ----------

         3          2          1
         2          2          3

2 rows selected.
created.

         A B C
---------- ---------- ----------

         3          2          1
         2          2          3

2 rows selected.

(sorry for the separate script/output. I ran this from Toad.

HTH,
  Ed Received on Wed Apr 30 2008 - 12:01:48 CDT