Update using other tables data

[Scorpy]

Thanks to Mchl I can now retrieve the value of the total workers within every building in every town that a user owns, however when trying to create another statement to do something similar I hit yet another dead end.

 

What I am attempting to do now is to update the users gold (user_gold) column (users_gold = users_gold + TotalPopulation - TotalWorkers).

 

      UPDATE users 
      SET 
         user_gold = (
            user_gold 
            + 
            (SELECT SUM(town_population) FROM towns WHERE town_owner_id = '1' AND town_id <= '2500') 
            - 
            ( 
               SELECT SUM(b.building_workers) FROM buildings AS b 
               INNER JOIN towns AS t ON (b.building_town_id = t.town_id AND t.town_id <= '2500') 
               INNER JOIN users AS u ON (t.town_owner_id = u.user_id) 
               WHERE u.user_id = '1' 
            )
        ) 
      WHERE users.user_id = '1'

 

To elaborate on the above, as there are too many towns to run through it takes 4-5 seconds on my machine, so I have it handling a set range of town_ids to speed it up and I just have the file running several times per second with different ranges for speed. In the aboves case it's going to be handling the town_ids between 1 and 2500. I have the user_id in for the time being to.. well, i'm not entirely sure why it's actually there at this moment in time, I'm over tired hehe.

 

I really hope this makes sense and that someone can help me along with this.

[Scorpy]

More information that might help (or in a possibly clearer way).

 

To get the empires population:

SELECT SUM(town_population) as TotalPopulation FROM towns WHERE town_owner_id = ?

 

To get the empires workers: (Thanks again to Mchl)

SELECT SUM(b.building_workers) AS TotalWorkers

FROM buildings AS b

INNER JOIN towns AS t ON (b.building_town_id = t.town_id)

INNER JOIN users AS u ON (t.town_owner_id = u.user_id)

WHERE u.user_id = ?

 

The statement i'm trying uses the above two, to scan through the town_ids (for the first 2500 ids in the table) and apply the gold onto the users user_gold column for user_gold + (empire population - empire workers).

 

I hope that's more clear I used quote instead of code so I can make it more readable aswell.

[Scorpy]

Do any of you experts need any more information for my problem? I would love some pointers orany type of help with it if possible please.

[Scorpy]

In another attempt i'll put up some more information and show my testing. IF there is something else needed then please tell me, as I don't think I have left anything out within my posts so far...

 

Here is a very stripped down version that I am trying to do, i'll post results of it after. In the proper version of the below it has all the calculations within for updating the town columns, but all that is working so I didn't want to place too much in here and waste anyones time reading it

 

   $strSQL = "
      UPDATE 
         towns 
      LEFT JOIN 
         users 
      ON 
         (users.user_id = towns.town_owner_id ) 
      SET 
         users.user_gold = users.user_gold + towns.town_population 
   ";
   mysql_query( $strSQL );
   echo "[TestGoldGain()] ".mysql_info().".<br />";

 

This gives me:

 

[TestGoldGain()] Rows matched: 1 Changed: 1 Warnings: 0.

 

And then when I do a select after for information:

 

Array
(
    [town_id] => 1
    [town_owner_id] => 1
    [town_population] => 2000
    [user_id] => 1
    [user_gold] => 2000
)
Array
(
    [town_id] => 2
    [town_owner_id] => 1
    [town_population] => 50
    [user_id] => 1
    [user_gold] => 2000
)
Array
(
    [town_id] => 3
    [town_owner_id] => 1
    [town_population] => 50
    [user_id] => 1
    [user_gold] => 2000
)
Array
(
    [town_id] => 4
    [town_owner_id] => 0
    [town_population] => 10.1111
    [user_id] => 
    [user_gold] => 
)

 

So out of those four towns, user_id 1 is assigned to two of them, the statement works to the extent that it will update the user_gold column from the FIRST town owned by that user but it will ignore the rest.

 

Please can someone loan me some help/advise with this, if more information is needed then I will GLADLY give whatever you need!

[fenway]

So out of those four towns, user_id 1 is assigned to two of them, the statement works to the extent that it will update the user_gold column from the FIRST town owned by that user but it will ignore the rest.

I don't understand... does the equivalent select query pull all of the towns?

[Scorpy]

The aim is to update two tables within one query, first the query calculates and updates all of the town variables that are needed such as population growth etc, then at the end of the query it updates the users table (via LEFT JOIN users ON user_id = town_owner_id) that owns the town if one should exist. The problem is that it updates the user for the first town that they own and not them all. Just in the sql statement I showed above i had stripped out all of the towns columns being updated to make it easier on the eyes.

 

I hope that makes more sense.

[fenway]

I understand what you mean, but I'm trying to figure out if the select statement is actually getting back more than the "first" town for each user.