SQL通过内部联接插入 [英] SQL Insert Into with Inner Join
本文介绍了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.com | 64557812 | passone
2 | Gong My | Gong | gong45@gmail.com | 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
这篇关于SQL通过内部联接插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文