Hybrilynx Hybrilynx - 22 days ago 6
MySQL Question

MySQL update table1 by table1 + table2 then set table2 to 0

I have tables

resources
,
building
. My query should explain what I'm trying to do:

update building b, resources r
set
r.wood = r.wood + b.storage,
b.storage = 0
where
b.castle_id = r.castle_id and
b.name = 'wood_farm'


I've tried other ways like using join or swap
building
and
resources
but nothing works. I tried something like
r.wood = r.wood + (select sum(storage) etc)
but that doesn't work for some reason. Besides I wouldn't be able to compare aliases (at least for what I tried).

What doesn't work? There are 2 buildings named
wood_farm
. They are linked to resources via
castle_id
. Lets say 2 buildings have
storage = 5
. This query will make resources equal 5 instead of what I want which is 10 (5 storage + 5 storage). Now both wood_farm buildings storage will equal 0.

I can understand why this doesn't work, but I can't find a solution.

building | id, castle_id, name, storage, etc, speed, etc, deleted, etc
resources | id, castle_id, wood, stone, etc, etc, deleted, etc
castle | id, other stuff, etc


MySQL version

innodb_version 5.6.34
protocol_version 10
version 5.6.34-log
version_comment Source distribution
version_compile_machine x86_64
version_compile_os Linux

Answer Source

I tried this (adding another subselect who makes the SUM of storage):

    UPDATE building b, resources r
          , (SELECT CASTLE_ID, NAME, SUM(STORAGE) AS STORAGE FROM building GROUP BY CASTLE_ID, NAME) b2
          SET r.wood = r.wood+b2.storage, b.storage=0
    WHERE b.castle_id = r.castle_id AND b.name = 'wood_farm'
           AND b2.castle_id=r.castle_id AND b2.name=b.name
     ;

and he did the update as requested. But I think you should do some checks to be sure it always do it.

Output:

ID  CASTLE_ID   NAME    STORAGE
1   2   wood_farm   0
1   3   iron_farm   10

ID  CASTLE_ID   WOOD
1   2   19
1   3   6

Sample data:

CREATE TABLE BUILDING (ID INT, CASTLE_ID INT, NAME VARCHAR(20), STORAGE INT);
CREATE TABLE RESOURCES (ID INT, CASTLE_ID INT,  WOOD INT);
INSERT INTO BUILDING VALUES (1, 2, 'wood_farm', 10);
INSERT INTO BUILDING VALUES (1, 3, 'iron_farm', 10);
INSERT INTO BUILDING VALUES (1, 2, 'wood_farm', 4);
INSERT INTO RESOURCES VALUES (1, 2, 5);
INSERT INTO RESOURCES VALUES (1, 3, 6);