MYSQL从table1插入数据到table2，前提是table1.value1不等于table2.value1

Question

INSERT INTO `dictionary2` (word,verb)
SELECT SUBSTRING(word FROM 2)
FROM `dictionary1`
WHERE `dictionary1`.word LIKE "w%"

我有两个表，dictionary1(word) 和dictionary2(word,verb)。

• 我想将dictionary1中的值插入到dictionary2中，其中单词以“w”开头，并且该值不存在于dictionary2.word中。
• 在同一个插入中，我想将dictionary2.verb 的值设置为1。

Answer 1

你可以使用这个:

INSERT INTO dictionary2 (word, verb)
SELECT dictionary1.word, 1
FROM
  dictionary1 LEFT JOIN dictionary2
  ON dictionary1.word = dictionary2.word
WHERE
  dictionary1.word LIKE 'w%'
  AND dictionary2.word IS NULL

请参阅 fiddle here .