2

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`);
Moe Epo
  • 92
  • 9

2 Answers2

0

For the on duplicate key to trigger, there has to be a key:

ALTER TABLE unit_storage ADD CONSTRAINT key1 UNIQUE (user_id, diagram_id)

Here's a working example on SQL Fiddle.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thank you both. I think this answer's solution is more neat and perfomant, I am accepting this answer. – Moe Epo Apr 06 '18 at 10:00
0
INSERT INTO unit_storage (user_id,diagram_id,amount)
SELECT Min(uf.user_id),
       Min(uf.diagram_id),
       sum(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
Group by uf.user_id,
       uf.diagram_id
  UPDATE unit_storage.amount=unit_storage.amount+uf.amount
Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27