Inserting Hierarchical Rows Duplicate Error

SQL
1

Hi there,

When trying to run the following queries

INSERT INTO orders(customer_id, order_date, status)
VALUES (1, “2020-01-02”, 3);

INSERT INTO order_items
VALUES
(LAST_INSERT_ID(), 1, 1, 2.95),
(LAST_INSERT_ID(), 1, 1, 3.95);

I get an error on the execution of the second query.
Error Code: 1062. Duplicate entry ‘11-1’ for key ‘order_items.PRIMARY’ 0.000 sec

Any idea?
Thanks

2

Is it possible that you are using the same LAST_INSERT_ID as the primary key for the both of the rows that you are inserting into order_items? Each row needs to have a unique primary key.

You may want to specify the column names in your second INSERT INTO statement, like you did with the first one, just to make sure that your values are lining up with the correct columns.

3

Yes, I got this problem also and its a typo! Your second set of values should be (LAST_INSERT_ID(),2,1,3.95).
Table order_items has a primary key based on (order_id, product_id), so you cant add that second record as both the order_id (from LAST_INSERT_ID()) and the product_id (1) would be the same as the first record inserted. That is what is causing the error.
(In case you are wondering why you cant have muliple products for the same order, you can, but you have to increase the quantity, you cant add duplicate primary keys.)
Hope that helps!
Patrick