OldWest Posted November 17, 2010 Share Posted November 17, 2010 Can anyone just have a look at tell me if my query and tell me if it seems optimal and optimized for what I am trying to do. I've got 2 tables: locations_all sys_states I need to update a blank field locations_all.state_id to sys_states.id where locations_all.city_state equals sys_states.stateName Here is my current query: UPDATE locations_all AS a JOIN sys_states AS b ON a.city_state = b.stateName SET a.state_id = b.id Does it seem efficient this way? Link to comment https://forums.phpfreaks.com/topic/219008-new-set-of-eyes-on-my-query-update-set-2-tables/ Share on other sites More sharing options...
OldWest Posted November 17, 2010 Author Share Posted November 17, 2010 I did some manual data comparison and checking. Based on my test of about 40,000 records, it seems to work fine, but if anyone thinks differently please let me know! I would hate to find data anomalies! Link to comment https://forums.phpfreaks.com/topic/219008-new-set-of-eyes-on-my-query-update-set-2-tables/#findComment-1135778 Share on other sites More sharing options...
mikosiko Posted November 17, 2010 Share Posted November 17, 2010 it is good... that is the way to do it.... just one observation based on your description: .... I need to update a blank field ..... you want to do the update ONLY if the locations_all.state_id is blank (empty, null or 0.. depending of the data type)... or you want to update it always? If the first case you need a condition (WHERE clause) in that UPDATE ... otherwise you are good to go Link to comment https://forums.phpfreaks.com/topic/219008-new-set-of-eyes-on-my-query-update-set-2-tables/#findComment-1135780 Share on other sites More sharing options...
OldWest Posted November 17, 2010 Author Share Posted November 17, 2010 Hey mikosiko, Thanks for the great feedback I see now how it could have been a disaster had I needed to keep existing data in the locations_all.state_id field! All locations_all.state_id fields where blank. And from my manual review and check everything seems to have been updated accurately. It took a lot of guts for me to run this even on test tables! I'll call this one SOLVED! Link to comment https://forums.phpfreaks.com/topic/219008-new-set-of-eyes-on-my-query-update-set-2-tables/#findComment-1135783 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.