how to update table based on another table in mysql?

Question

i have 2 tables as fallow:

users
zip      state      city
89012    
45869     0
....


zips
zip      state      city
89012    NV         lv
45869    MI         ca
....

i would like to update users: state and city with the state and city from zips based on the zip from the users table in an efficient way

the city in users table is empty but the state can also be 0 or empty

any ideas?

thanks

Answer 1

Are you trying to update all of the existing rows in bulk?

If so, here's one way to do the update:

update users u
  inner join zips z on z.zip = u.zip
set u.state = z.state,
  u.city = z.city;