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!
update query question
Moderator: General Moderators
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
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...
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...