SQL通过内部联接插入

本文介绍了SQL通过内部联接插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我要进行插入查询，该查询具有对Users表的内部联接.

I want to make my insert query which has an inner join to the Users table.

表的示例如下:

用户:

id | fullName | preferredName | email | mobile | password 1 | Pan Lim | Lim | limpan45@gmail | 64557812 | passone 2 | Gong My | Gong | gong45@gmail | 61345671 | passtwo

订单:

id | userid(Foreign key of "id" from Users | timestamp 1 | 1 | 2016-06-10 11:45:31

我正在尝试插入仅与从Users表中了解用户ID有关的Orders.它显示如下:

I'm trying to insert into Orders relating to only knowing the userid from the Users table. It show like this:

订单:

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

但是当我对SQL查询进行测试时，该查询有错误.

But when I test on my SQL query, it has error on this query.

INSERT INTO Orders (id, userid, timestamp) SELECT Orders.id, Orders.userid, Orders.timestamp FROM Users INNER JOIN Orders ON Orders.id = Users.id

推荐答案

如果订单"表中的ID是不自动递增.

If your id is not auto increment in Orders table.

INSERT INTO orders ( id,userid, timestamp) SELECT o.userid , o.timestamp FROM users u INNER JOIN orders o ON o.userid = u.id

如果您的ID是订单"表中的自动递增.

If your id is auto increment in Orders table.

INSERT INTO orders ( userid, timestamp) SELECT o.userid , o.timestamp FROM users u INNER JOIN orders o ON o.userid = u.id