MySQL通过table1 + table2更新table1然后将table2设置为0

Question

我有表资源、建筑。我的查询应该解释我想要做什么:

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'

我尝试过其他方法，例如使用连接或交换building和resources，但没有任何效果。我尝试了类似 r.wood = r.wood + (select sum(storage) etc) 但由于某种原因不起作用。此外，我无法比较别名(至少对于我尝试过的)。

什么不起作用？有 2 栋名为 wood_farm 的建筑物。它们通过 caSTLe_id 链接到资源。假设 2 栋建筑物的存储空间 = 5。此查询将使资源等于 5，而不是我想要的 10(5 个存储 + 5 个存储)。现在，wood_farm 建筑物的存储量将等于 0。

我可以理解为什么这不起作用，但我找不到解决方案。

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版本

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 1

我尝试了这个(添加另一个子选择来计算存储的总和):

    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
     ;

他按照要求进行了更新。但我认为你应该做一些检查以确保它总是能做到。

输出:

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

示例数据:

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);