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

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