0

db-fiddle

I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like

scanId, customersId, productId
1,1,1
1,1,2
1,2,3
2,3,4
2,1,5
2,1,6

I want to insert a new order for every distinct scanId,customersId, and then for every order insert a productId into orderDetails. So given temp you would see 4 orders created, with the 1st and 4th order containing 2 productIds each.

I am used to SQL Server where we can utilize the OUTPUT clause of an INSERT to tie identities back to an initial INSERT. However MySQL doesn't have anything like that. Following advice from https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids

I came up with the following:

INSERT INTO orders(customersId)
SELECT customersId
FROM temp
GROUP BY scanId, customersId
ORDER BY scanId, customersId;

INSERT INTO orderDetails(orderId, productId)
SELECT 
    DENSE_RANK() OVER(
        ORDER BY scanId, customersId
    ) + LAST_INSERT_ID() -1, productId
FROM temp;

The trick being DENSE_RANK() OVER( ORDER BY scanId, customersId ) + LAST_INSERT_ID() -1 constructs the orderId.

Will this always work? Specifically, will MySQL always assign autoincrement ids to inserts based on the ORDER BY and then utilzing a windowed function on the partition and order always tie back to the inserted id?

1 Answer 1

0

You don't need to stand on your head to get the id:

Step 1: Populate Orders in a single statement:

INSERT INTO Orders (scanId, customerId)   -- Leave out; `id` it will be set
    SELECT DISTINCT scanId, customerId FROM temp;

Step 2: Populate the other table:

INSERT INTO OrderDetails (orderId, productId)
    SELECT Orders.id AS orderId,
           temp.productId
        FROM Orders
        JOIN temp USING (scanId, customerId);

It would be helpful to have

temp:  INDEX(scanId, customerId)
2
  • Orders doesn't store scanId so there is no way to link an order to productIds
    – ParoX
    Jan 9, 2021 at 13:19
  • Sounds like you need an extra step. That is, use another temp table with the scanId.
    – Rick James
    Jan 9, 2021 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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