Page 1 of 1

update query question

Posted: Tue Apr 13, 2004 10:57 am
by liljester
I need to update a row in a table, but im not sure at the time of query which col i need to update in the row...

here are the 2 tables involved:
buildings (table)
building_1
building_2
building_3
building_4
building_5

buildings_queue (table)
building_type (possible values : 1, 2, 3, 4, 5)
time_left

what im trying to do is when buildings_queue.time_left <= 0, I want to increment buildings.building_x by 1 based on what is in buildings_queue.building_type

ps: I know that the building table design is wrong, but its not something i can change right now =/

Thanks!

Posted: Tue Apr 13, 2004 11:05 am
by kettle_drum
for($x=1;$x<6;$x++){
mysql_query("UPDATE buildings, building_queue SET buildings.building_".$x." = building_queue.building_type WHERE building_queue.time_left < '0.1'");
}

Could use something like that, would have to test it though. Cant think of a way to check all of them in one query nicely.

Posted: Tue Apr 13, 2004 11:14 am
by liljester
Thats the kind of soulution im using right now... a big ugly php loop. if possible i was wanting to avoid using a php loop, and using a SQL query

Posted: Tue Apr 13, 2004 11:18 am
by kettle_drum
Well im sure there must be a way to do it all in one query, but i would do some time checks on it, as looping a simple query like this is fairly fast - expecially if the other sql query is a monster - which im sure it will be.

Posted: Tue Apr 13, 2004 11:40 am
by liljester
its been my experiance (limited as it may be) that when dealing with lots of rows, its much faster to let mysql do the work when possible... ive never seen a time when looping through a bunch of rows and updating them as needed in php is faster than running a handfull of queries.

mabe i should explain a lil more =) there will be thousands of rows, and i will be joining and checking data in more tables than ive shown.
the file that i currently have loops through all rows in the buildings_queue table (could be several thousands of rows in the future), then checks all the info, then does some calcs, then updates as needed. thats 5 (give or take) queries per row in the buildings_queue... i want a better way...

the file im working on now, has a toal of 8 queries.. and it does everything i need it to do except updating the buildings.building_x col.. everything else is done... ive tried using CASE satements (in an update statement), but mysql doesnt seem to like them...