Skip to content
Advertisement

I wanna create a new table from the data of two tables

I have a A table to store the product name, the quantity and input day of goods in stock. In adverse conditions, invoices for the above products will be sent later. I will put the product name and invoice quantity in B table. The problem here is that I want to check the quantity of goods with invoice and without invoice. Updating goods with invoices will follow FIFO.

Example:

Table A

id good_id num created_at
1 1 10 2021-09-24
2 1 5 2021-09-25

Table B

id good_id num_invoice
1 1 12

I solved it by creating a new C table with the same data as the A table

Table C

id good_id current_number created_at invoice_number
1 1 10 2021-09-24 null
2 1 5 2021-09-25 null

Then I get the data in table B group by good_id and store it in $data. Using php to foreach $data and check condition:

I updated C table ORDER BY created_at DESC limit 1 as follows:

  • if (tableC.current_num$data['num'] < 0) then update current_number = 0, invoice_number = $data['num']tableC.current_num. Update value $data['num'] = $data['num']tableC.current_num

  • if (tableC.current_num$data['num'] > 0) or (tableC.current_num – $data[‘num’] = 0) then update current_number = tableC.current_num$data['num'], invoice_number = $data['num'].

table C after update

id good_id current_number created_at invoice_number
1 1 0 2021-09-24 10
2 1 3 2021-09-25 2

I solved the problem with php like that. However, with a dataset of about 100,000 rows, I think the backend processing will take a long time. Can someone give me a smarter way to handle this?

Advertisement

Answer

Updated solution for MySQL 5.7:

Test case to support MySQL 5.7+, PG, MariaDB prior to 10.2.2, etc:

Test case for MySQL 5.7+, etc

For MySQL 5.7: This replaces the window function (for running SUM) and uses derived tables instead of WITH clause.

For databases that handle functional dependence in GROUP BY properly, we could just GROUP BY t2.id (the primary key of tableA) and remove the MIN(t2.id) and the MIN(t2.num).

Like this:

Test case

Original Answer using window functions and WITH clause:

Here’s a test case with PG 13, but works fine with MySQL 8 or MariaDB 10.2.2+.

Note: I left this as just a query that generates the requested detail. It’s not clear the 3rd table is necessary. This can be used to update (or create) that table, if needed.

Test case:

Working test case

CTE terms:

  1. cte1 – calculate the total requested goods
  2. cte2 – Calculate the running balance based on running inventory by date

Finally, we use cte2 to determine the goods allocated and remaining, requested by the question.

The result:

Note: I added one additional onhand entry for 7 goods (id = 3) to test an edge case.

Setup of the test case:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement