CODEWITHSUNDEEP
phpmysql
john
john

Reputation: 1330

mysql insert into if not exists from seperate table

I'm trying to insert an array of new contacts from a temporary import table into our primary customer database. Before it is inserted, we want to first check if the contact exists on our blacklist. If it does exists, we do not want to insert it into the primary table.

I first pull the contacts from temp table:

SELECT `email` FROM `import_contacts`

Then I want to insert those contacts into the primary table ONLY AFTER it has been "scrubbed" or checked against the blacklist. The way I have it currently:

INSERT INTO `contacts` (`email`) 
VALUES ('".implode("','','',''),('",$email)."','','','') 
WHERE...

I got confused when it occurred to me that imploding the array like I have implodes ALL contacts, including those on the blacklist. So even if I were to get the WHERE statement to work, it would be wasteful and full of ambiguous data.

Is there a way to insert the contacts into the primary table after it has been checked against the blacklist table using one sql statement?

Any help would be greatly appreciated!!

Upvotes: 1

Views: 756

Answers (2)

Lee Davis
Lee Davis

Reputation: 4756

INSERT INTO `contacts` (`email`)
SELECT email FROM `import_contacts`
WHERE `import_contacts.email` NOT_IN(`SELECT email FROM my_blacklist`);

Upvotes: 5

user745235
user745235

Reputation:

Try using insert select from MySQL.

It will run a insert based on a select.

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

Example from dev.mysql.com:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Upvotes: 0

Related Questions