Search code examples
sqldatabasesubqueryderby

Subquery to insert data into lookup table if it doesn't already exist

I want to insert the primary keys from two tables into a lookup table. How do I only insert the data returned from the tables if it does not already exist in the table? I.e. if the query returns 1,1 and 1,1 already exists in the table, don't insert. My query looks like this: 

INSERT INTO tblUserLookup(name,class)
SELECT tblName.id,tblClass.id FROM tblName,tblClass
WHERE name='bob' AND class='grade4';

Thanks!

Solution

  • You can use NOT EXISTS to check if data is already existed in the table

    INSERT INTO tblUserLookup(name,class)
select idName, idClass 
from (SELECT tblName.id idName,tblClass.id idClass FROM tblName,tblClass
      WHERE name='bob' AND class='grade4') tmp
where not exists (select name from tblUserLookup tmp1
               where tmp1.name = tmp.idName and tmp1.class = tmp.idClass);