Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

mysql - SQL Insert Into with Inner Join

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:

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.

INSERT INTO Orders (id, userid, timestamp) 
SELECT Orders.id, Orders.userid, Orders.timestamp FROM Users INNER JOIN Orders ON  Orders.id = Users.id
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...