Updating 1 field, 30,000+ rows from a different tables field value

[slyte33]

I own a RPG game, and there are 2 tables for weapons/armour.

I join these tables, one being a table that contains the description, name, and everything of the armoury and one table that contains the owned items in the game.

I have them joined by all_items ID = player_items item_ID

 

What i'm trying to do is update the player_items table(field is called sockets) from the all_items table.

 

all_items = 1 field named 'sockets', each armoury has different sockets.

player_items = 1 field named 'sockets_left', sockets can be used and thus go down.

 

I want to update player_items sockets to match the all_items sockets based on joining table by ID's.

 

Example:

$query = $db->execute("select all_items.*, player_items.* from all_items, player_items where all_items.id=player_items.item_id");
foreach($query as $items)
{
$update = $db->execute("update items set sockets_left=sockets_left+$items[sockets] where item_id=$items[id]");
}

 

The above code does not work, it instead gives each item 2k+ sockets.

 

I hope i explained this well, thanks

[Mchl]

UPDATE player_items AS p INNER JOIN all_items AS i ON p.item_ID = i.ID SET p.sockets_left = p.sockets_left - i.sockets

 

Like this? Although it doesn't really make sens to me. Why not just have a total amount of sockets of a player and just substract sockets for each items they carry?

[slyte33]

UPDATE player_items AS p INNER JOIN all_items AS i ON p.item_ID = i.ID SET p.sockets_left = p.sockets_left - i.sockets

 

Like this? Although it doesn't really make sens to me. Why not just have a total amount of sockets of a player and just substract sockets for each items they carry?

 

Sorry I don't quite understand what your saying, but other than that your code worked great! Thank you very, very much