New set of eyes on my Query - UPDATE, SET 2 Tables

[OldWest]

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?

 

[OldWest]

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!

[mikosiko]

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

[OldWest]

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!