CODEWITHSUNDEEP
mysql
stackr
stackr

Reputation: 2742

MySQL only updates a from first row instead of all rows

i'm having trouble with a MySQL UPDATE query which isn't doing what i want.

I have 3 tables and they look like this (relevant portion only):

[inventory table]
id
population

[item table]
id
populationGeneration

[items_for_inventory table]
id
inventory_id
item_id
amount

What i am trying to accomplish is the following. Inventory has an id and a population count column. The population is a value which needs to be altered and not overwritten.

Item contains a id and a generation count column. This means that for each item with this id, this is the amount of population it generates and should be added to Inventory.population.

Last is a linking table which contains the id of the inventory and the id of the item and last the amount of this item.

A simple example inventory population = 5000

item 1 populationGeneration = 1

item 2 populationGeneration = 3

items_for_inventory links the item to the inventory by id and specifies that the inventory contains for example 20 pieces item 1 and 3 pieces of item 2. so:

items_for_inventory id 1, amount 20

items_for_inventory id 2, amount 3

When i run the desired script i want ALL items that contain populationGeneration to be added to the initial population, which means:

inventory population = 5029 (20x1 and 3x3).

The script that i tried that doesn't work (i tried many):

UPDATE inventory ivt, 
        (SELECT 
            tmp.inventory_id, 
            tmp.amount * tmp.populationGeneration AS population
            FROM (SELECT ifi.id AS ifi_id, 
                inventory_id,
                item_id, 
                amount, 
                populationGeneration 
                FROM items_for_inventory ifi 
                JOIN item itm ON ifi.item_id = itm.id) tmp
       ) AS tmps
SET ivt.population = tmps.population 
WHERE ivt.id = tmps.inventory_id AND itm.id = tmps.item_id;

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

If I understand correctly, you need to aggregate to the inventory level before doing the assignment. When multiple rows match the update, one arbitrary matching row is chosen for the update. They don't accumulate.

Fortunately, this is easily fixed:

UPDATE inventory ivt JOIN
        (SELECT ifi.inventory_id,
                sum(ifi.amount * itm.populationGeneration) AS population
            FROM items_for_inventory ifi JOIN
                 item itm
                 ON ifi.item_id = itm.id
            GROUP BY ifi.inventory_id
       ) newp
       ON ivt.id = newp.inventory_id
    SET ivt.population = newp.population ;

Upvotes: 1

Related Questions