如果另一个表中不存在值，则mysql插入 [英] mysql insert if value not exist in another table

问题描述

我有两个表，其值存储为VARCHAR.
我正在填充表，如果其他表中不存在这些值，则只想在其中一个表中插入值.
像这样:

I have two tables that store value as VARCHAR.
I'm populating table and I want just insert values in one of tables if they are not exist in other table.
Something like:

INSERT IF IS EMPTY(SELECT * FROM t1 where v='test') INTO t2 (v) VALUES ('test')

我该怎么做?

推荐答案

您需要使用某种

You need to use some type of INSERT...SELECT query.

更新(在澄清之后)::例如，如果t1中尚不存在相应的行，则下面是在t2中插入行的方法:

Update (after clarification): For example, here is how to insert a row in t2 if a corresponding row do not already exist in t1:

INSERT INTO t2 (v)
  SELECT temp.candidate
  FROM (SELECT 'test' AS candidate) temp
  LEFT JOIN t1 ON t1.v = temp.candidate
  WHERE t1.v IS NULL

要在同一查询中插入多行，恐怕没有比这更好的

To insert multiple rows with the same query, I 'm afraid there is nothing better than

INSERT INTO t2 (v)
  SELECT temp.candidate
  FROM (
      SELECT 'test1' AS candidate
      UNION SELECT 'test2'
      UNION SELECT 'test3' -- etc
  ) temp
  LEFT JOIN t1 ON t1.v = temp.candidate
  WHERE t1.v IS NULL

原始答案

例如，这将从table1中满足WHERE子句的所有行中提取other_column，并将行插入到table2中，并将值用作column_name.它将忽略重复的键错误.

For example, this will take other_column from all rows from table1 that satisfy the WHERE clause and insert rows into table2 with the values used as column_name. It will ignore duplicate key errors.

INSERT IGNORE INTO table2 (column_name)
  SELECT table1.other_column
  FROM table1 WHERE table1.something == 'filter';

这篇关于如果另一个表中不存在值，则mysql插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！