25

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
Improve this question
4
  • 3
    whats the "some" error?
    – Tik
    Commented Jun 10, 2017 at 4:31
  • 2
    i think you on should be ON Orders.userid = Users.id
    – JYoThI
    Commented Jun 10, 2017 at 4:36
  • 3
    a good way to troubleshoot an insert query that's failing with a mysterious error that cannot be named is to make sure the select query runs on its own.
    – Bryan Newman
    Commented Jun 10, 2017 at 6:57
  • 3
    Not sure if they are made up, but we can see Lim's and Gong's email addresses and phone numbers in your question. If real it'd be better to obfuscate them.
    – eberbis
    Commented Oct 30, 2018 at 15:18

2 Answers 2

Reset to default
45

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
Improve this answer
8

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:

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.