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);

Thank you.




additional info:
I've seen conditional inserts without values(....), as in

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
FROM clients
WHERE clients.client_id =
suppliers.supplier_id);

md wrote:
Look up 'insert when'.

--
Peter

md wrote:
Look up 'insert when'.

--
Peter

md wrote:
Look up 'insert when'.

--
Peter

md wrote:
Look up 'insert when'.

--
Peter

On Apr 29, 11:08 am, md <mardahl2... (AT) yahoo (DOT) com> wrote:
(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.)
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

On Apr 29, 11:08 am, md <mardahl2... (AT) yahoo (DOT) com> wrote:
(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.)
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

On Apr 29, 11:08 am, md <mardahl2... (AT) yahoo (DOT) com> wrote:
(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.)
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

On Apr 29, 11:08 am, md <mardahl2... (AT) yahoo (DOT) com> wrote:
(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.)
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