Re: [GENERAL] sql question; checks if data already exists before
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] sql question; checks if data already exists before inserted
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. signature.asc Description: Digital signature
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster