Thread: sql question; checks if data already exists before inserted

hi, there. i'm trying to write a SQL statement which does the following
things.

1. checks if data already exists in the database
2. if not, insert data into database otherwise skip.

for example, i'd like to insert a student called 'Michael Jordan' whose
ID is 'JORDANMICHAEL' only if the id, 'JORDANMICHAEL' does not already
exist in the database. anyway, my query looks like...

insert into student (studentid, fname, lname)
select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
studentid not in
(select studentid from student);

however, this does not seem to work. it does not insert data even if it
does not exist in the database. hmm!

any clue?

thanks.

On Wed, Jun 21, 2006 at 10:59:42PM -0700, nuno wrote:
> insert into student (studentid, fname, lname)
> select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
> studentid not in
> (select studentid from student);
>
> however, this does not seem to work. it does not insert data even if it
> does not exist in the database. hmm!

There are no NULL studentid's, right? Because that would make NOT IN do
something other than you think. Perhaps NOT EXISTS would work better.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

nuno wrote:
> hi, there. i'm trying to write a SQL statement which does the
> following things.
>
> 1. checks if data already exists in the database
> 2. if not, insert data into database otherwise skip.
>
> for example, i'd like to insert a student called 'Michael Jordan'
> whose ID is 'JORDANMICHAEL' only if the id, 'JORDANMICHAEL' does not
> already exist in the database. anyway, my query looks like...
>
> insert into student (studentid, fname, lname)
> select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
> studentid not in (select studentid from student);
>
> however, this does not seem to work. it does not insert data even if
> it does not exist in the database. hmm!
>
> any clue?

Your query is not doing what you think it's doing.  Try running just the
select portion of the query you provide above (minus the "insert into"
part.)  You are selecting from student where studentid not in (select
studentid from student).  That will always return the empty set, since
you are looking at the same column and the same table in both the inner
and outer selects.

What you want to do is create a primary key on your student table.  Then
try to do the insert, catching the "not unique" error return.

--
Guy Rouillier

nuno wrote:

>hi, there. i'm trying to write a SQL statement which does the following
>things.
>
>1. checks if data already exists in the database
>2. if not, insert data into database otherwise skip.
>
>
>
Check this thread :

http://archives.postgresql.org/pgsql-general/2005-10/msg01787.php