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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

md - 29 Apr 2008 16:08 GMT

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

Reply to this Message

Peter Nilsson - 30 Apr 2008 04:27 GMT

> This is what I wish I could to do.
>
[quoted text clipped - 3 lines]
>
> Thank you.

Look up 'insert when'.

--
Peter

Reply to this Message

Ed Prochak - 30 Apr 2008 18:01 GMT

> 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

Reply to this Message