Update with subquerie help!

[RockyLou]

I'm doing this update statement here:

 

mysql_query("UPDATE hr_user as u, hr_building as b SET
u.xp=u.xp + b.land + $char_xp WHERE u.userid=b.userid");

 

The way I get $char_xp is by a loop for the users and I want to eliminate using loops.  Is there a way I can use a subquerie to retreive the value? Note: there can be more than 1 field in the other table

 

mysql_query("UPDATE hr_user as u, hr_building as b SET
u.xp=u.xp + 
             b.land + 
             (select sum(m.xp) from hr_military as m)
WHERE u.userid=b.userid AND u.userid=m.userid");

 

 

I need something like that, any ideas?

 

 

[fenway]

Sounds like you need a where clause in that subquery, no?

[RockyLou]

yeah but would it work with the parent query ?

 

mysql_query("UPDATE hr_user as u, hr_building as b SET
u.xp=u.xp + 
             b.land + 
             (select sum(m.xp) from hr_military as m WHERE m.userid=u.userid)
WHERE u.userid=b.userid AND u.userid=m.userid");

[fenway]

Should be just fine...

[RockyLou]

I donno for some reason it wont work..having problems with the subquerie

[bubblegum.anarchy]

what is the final `AND u.userid=m.userid` all about?

 

written differently:

UPDATE hr_user
     INNER JOIN hr_building ON hr_user.user_id = hr_building.userid
SET hr_user.xp = hr_user.xp + hr_building.land + ( 
     SELECT sum(hr_military.xp) 
     FROM hr_military 
     WHERE hr_military.userid = hr_user.user_id )

[RockyLou]

Yeah the subquerie still doesn't work.  I don't really understand why though, really weird.

 

I checked all the names on the db to verify and they match.