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

score 8 · Accepted Answer · answered Jan 23 '12 at 19:18

8

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;

answered Jan 23 '12 at 19:18

Ike Walker

there should be the condition that `(city <> '' and state <> '') or (city <> '' and state <> '0')` or something like this – Patrioticcow Jan 23 '12 at 19:20
Certainly you can refine the query to not replace good data with bad, etc depending on your requirements. I was just working from the requirements you gave in the original question. – Ike Walker Jan 23 '12 at 21:20

1 Answers1