I run into a problem with this query:
INSERT INTO unit_storage (user_id,diagram_id,amount)
SELECT uf.user_id,
uf.diagram_id,
uf.amount
FROM unit_factory uf
WHERE uf.production_finish_time < /*current unix epoch seconds*/
AND uf.user_id = /*provided user id*/ ON DUPLICATE KEY
UPDATE unit_storage.amount=unit_storage.amount+uf.amount
Let's start with the example of that is happening and what is my desired effect…
As you may have guessed from the compared value, production_finish_time is integer column where the time in seconds is marking a certain date.
Take a look at unit_factory table rows:
+-----+----------+-------------+---------+-------------------------+--+
| id | user_id | diagram_id | amount | production_finish_time | |
+-----+----------+-------------+---------+-------------------------+--+
| 7 | 10 | 2 | 1 | 1521472903 | |
| 8 | 10 | 2 | 1 | 1521473704 | |
| 9 | 10 | 2 | 1 | 1521473729 | |
| 10 | 10 | 2 | 1 | 1521474294 | |
+-----+----------+-------------+---------+-------------------------+--+
After I run the query, unit_storage table becomes a copy of the unit_factory table:
+-----+----------+-------------+---------+--+
| id | user_id | diagram_id | amount | |
+-----+----------+-------------+---------+--+
| 16 | 10 | 2 | 1 | |
| 17 | 10 | 2 | 1 | |
| 18 | 10 | 2 | 1 | |
| 19 | 10 | 2 | 1 | |
+-----+----------+-------------+---------+--+
While the result I need is:
+-----+----------+-------------+---------+--+
| id | user_id | diagram_id | amount | |
+-----+----------+-------------+---------+--+
| 16 | 10 | 2 | 4 | |
+-----+----------+-------------+---------+--+
What is the correct query for the desired result?
Edit:
As requested, indexes for both tables:
--
-- Indexes for table `unit_storage`
--
ALTER TABLE `unit_storage`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `unit_factory`
--
ALTER TABLE `unit_factory`
ADD PRIMARY KEY (`id`);