With MySQL, how do I insert into a table on condition that the value does not exist in another table?

Question

I have a MySQL database and I would like to insert some values into one table, assuming that a particular value that I am inserting does not match a value in a different table.

Here is a simplified/example structure:

Table: invites
    id : int (auto-increment index)
    name : varchar
    message : varchar

Table: donotinvite
    name : varchar (index)

Is it possible to do a conditional insert of a 'name' and 'message' pair into the 'invites' table assuming the 'name' does not match any 'name' from the 'donotinvite' table with a single statement?

Something like this, perhaps?

INSERT INTO invites
    SET name = 'joe', message = 'This is an invite'
WHERE NOT EXISTS 
    (SELECT name 
    FROM donotinvite
    WHERE name = 'joe')

Solution 1

INSERT
INTO invites (name, message)
SELECT a.name, a.message
FROM (SELECT @name AS name, @message AS message) a
LEFT JOIN donotinvite d
ON a.name = d.name
WHERE d.name IS NULL

Solution 2

(I'm an MSSQL person, so not sure if all appropriate for MySQL)

INSERT INTO invites
(
    name, message
)
SELECT name = 'joe', message = 'This is an invite'
WHERE NOT EXISTS 
    (SELECT *
    FROM donotinvite
    WHERE name = 'joe')

In practice you might put all the Name / Message in a temporary table and then do:

INSERT INTO invites
(
    name, message
)
SELECT T.name, T.message
FROM MyTempTable AS T
WHERE NOT EXISTS 
(
    SELECT *
    FROM donotinvite AS DNI
    WHERE DNI.name = T.name
)

Solution 3

This would do the job, but I'm not sure it would peform that well. It might work for your application:

INSERT INTO invites(name, message) 
SELECT p.name, "This is an invite" 
FROM people AS p  
WHERE p.name NOT IN (SELECT name FROM donotinvite);

Do you have a table like people, a catalog of all the people you can send invites to?

Solution 4

INSERT INTO invites (name, message) 
SELECT 'joe', 'This is an invite' FROM (SELECT 1) t 
WHERE NOT EXISTS (SELECT * FROM donotinvite WHERE name = 'joe');

Solution 5

Here is a re-posting of jonstjohn's strangely deleted answer, slightly modified by me:

INSERT INTO invites (name, message) 
    SELECT 'joe','This is an invite' 
    FROM donotinvite 
    WHERE name <> 'joe'

EDIT: this doesn't do what I want :) .

View more solutions