SEO

#1 (**permalink**) 04-29-2008, 08:28 PM

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

#2 (**permalink**) 05-02-2008, 05:05 AM

md 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);
>
> Thank you.

Look up 'insert when'.

--
Peter

#3 (**permalink**) 05-02-2008, 05:05 AM

On Apr 29, 11:08 am, md <mardahl2...@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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

conditional insert with values; insert into .. values(...) where notexist (....);