mysql - 将 Mariadb 表中的数据插入到另一个表中

Question

如果 main_table 的 email 值为空，我似乎无法找到正确的语法来将数据从一个表插入到另一个表中。如果 main_table 电子邮件对于 id 来说是空的，那么我希望从以下位置插入该 id 的 email 辅助表:

MariaDB> SELECT * FROM `main_table`;
+------+---------------------+----------------------------------+------+
| Id   | Email               | Other                            | More |
+------+---------------------+----------------------------------+------+
| 1    | user1@somewhere.com | blah                             | A    |
| 2    |                     | needs email from secondary_table | B    |
| 3    | user3@someplace.com | blah                             | C    |
+------+---------------------+----------------------------------+------+
3 rows in set (0.09 sec)

MariaDB> SELECT * FROM `secondary_table`;
+------+---------------------+-------+
| Id   | Email               | Info  |
+------+---------------------+-------+
| 1    | user1@somewhere.com | blah  |
| 1    |                     | blank |
| 2    | user2@something.com | blah  |
| 2    |                     | blank |
| 3    | user3@someplace.com | blah  |
| 3    |                     | blank |
+------+---------------------+-------+
6 rows in set (0.09 sec)

在此示例中，main_table 中的 id 编号 2 的 email 为空。我正在尝试从插入到 main_table 的 secondary_table 中获取带有 email 的 id 。我试过:

INSERT INTO `main_table`
(`Email`)
VALUES
( SELECT `Email` FROM `secondary_table` WHERE `Id` IN
( SELECT `Id` FROM `main_table` WHERE `Email` == '') ) ;

一路上还有各种其他失败......也许这很简单，但我被困住了!

Answer 1

查看您的示例似乎您需要更新(通过连接)而不是插入
(假设main_table中的email字段为空)

update main_table
inner join secondary_table ON main_table.id = secondary_table.id 
set main_table.Email = secondary_table.Email               
where  main_table.Email is null     
and  secondary_table.email <> ''   

或假设 main_table 中的电子邮件字段为 =''

update main_table
inner join secondary_table ON main_table.id = secondary_table.id 
set main_table.Email = secondary_table.Email               
where  main_table.Email = ''
and secondary_table.email <> ''