Обсуждение: 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