Re: conditional insert with values; insert into .. values(...) where not exist (....);
Date: Wed, 30 Apr 2008 10:01:48 -0700 (PDT)
Message-ID: <052f6068-dabb-46ce-aa82-cda85f22bcf1@l64g2000hse.googlegroups.com>
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