使用 MySQL，如何在另一个表中不存在该值的情况下插入一个表?

2022-01-09 00:00:00 sql conditional insert mysql

我有一个 MySQL 数据库，我想在一个表中插入一些值，假设我插入的特定值与不同表中的值不匹配.

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)

假设名称"与donotinvite"表中的任何名称"都不匹配，是否可以通过单个语句将名称"和消息"对有条件地插入到邀请"表中?

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?

大概是这样的吧?

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

推荐答案

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

相关文章