MySQL SQL Insert Into with Inner Join
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44469503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Insert Into with Inner Join
提问by Lue Fang
I want to make my insert query which has an inner join to the Users table.
我想让我的插入查询与用户表有一个内部连接。
The example of the tables is like this:
表的例子是这样的：
Users:
用户：
id | fullName | preferredName | email | mobile | password
1 | Pan Lim | Lim | [email protected] | 64557812 | passone
2 | Gong My | Gong | [email protected] | 61345671 | passtwo
Orders:
订单：
id | userid(Foreign key of "id" from Users | timestamp
1 | 1 | 2016-06-10 11:45:31
I'm trying to insert into Orders relating to only knowing the userid from the Users table. It show like this:
我试图插入与只知道用户表中的用户 ID 相关的订单。它显示如下：
Orders:
订单：
id | userid(Foreign key of "id" from Users | timestamp
1 | 1 | 2016-06-10 11:45:31
2 | 2 | 2016-08-14 12:45:31
But when I test on my SQL query, it has error on this query.
但是当我测试我的 SQL 查询时，这个查询有错误。
INSERT INTO Orders (id, userid, timestamp)
SELECT Orders.id, Orders.userid, Orders.timestamp FROM Users INNER JOIN Orders ON Orders.id = Users.id
回答by VNT
If your id is not auto incrementin Orders table.
如果您的 id在 Orders 表中不是自动递增的。
INSERT INTO orders ( id,userid, timestamp)
SELECT o.userid , o.timestamp FROM users u INNER JOIN orders o ON o.userid = u.id
If your id is auto incrementin Orders table.
如果您的 id在 Orders 表中是自动递增的。
INSERT INTO orders ( userid, timestamp)
SELECT o.userid , o.timestamp FROM users u INNER JOIN orders o ON o.userid = u.id
回答by Mr Zach
If Orders.Id is unique your SQL query will fail. You are trying to insert one or more existing records into the Orders table so it will be duplicates of Id.. I'm not sure what you are trying to achieve, but this should work:
如果 Orders.Id 是唯一的，您的 SQL 查询将失败。您正在尝试将一个或多个现有记录插入到 Orders 表中，因此它将是 Id 的重复项。我不确定您要实现什么，但这应该有效：
INSERT INTO Orders ( userid, timestamp)
SELECT Orders.userid, Orders.timestamp FROM Users INNER JOIN Orders ON Orders.id = Users.id
Edit: Orders.Id should be an unique id for the record in orders and Users.Id should be an unique id for the record in the user table? in this case you cannot join on these two.
编辑：Orders.Id 应该是订单中记录的唯一 ID，而 Users.Id 应该是用户表中记录的唯一 ID？在这种情况下，你不能加入这两个。
