update query question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

update query question

Post 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!
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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...
Post Reply