22

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          | limpan45@gmail.com | 64557812 | passone
2  | Gong My  |  Gong         | gong45@gmail.com   | 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
Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
Lue Fang
  • 245
  • 1
  • 3
  • 5
  • 3
    whats the "some" error? – Tik Jun 10 '17 at 04:31
  • 2
    i think you on should be ON Orders.userid = Users.id – JYoThI Jun 10 '17 at 04: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 Jun 10 '17 at 06:57
  • 2
    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 Oct 30 '18 at 15:18

2 Answers2

42

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
VNT
  • 934
  • 2
  • 8
  • 19
7

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.

Mr Zach
  • 495
  • 4
  • 18