如何在mysql中基于另一个表更新表?

问题描述：

我有2张桌子作为休假:

i have 2 tables as fallow:

users
zip      state      city
89012    
45869     0
....


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

我想基于users表中的zip用zips中的state and city更新users: state and city

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

users表中的city为空，但state也可以为0或空

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

有什么想法吗?

谢谢

答

您是否要批量更新所有现有行?

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;