基于表A的a字段，生成其他字段

set_column_by_column.sql

update np_manager mm
  left join
  ( SELECT
      left(m.location_id, 2) * pow(10, 10) AS province_id,
      (CASE WHEN m.type >= '2'
        THEN left(m.location_id, 4) * pow(10, 8)
       ELSE 0
       END)                                   city_id,
      (CASE WHEN m.type >= '2'
        THEN left(m.location_id, 6) * pow(10, 6)
       ELSE 0
       END)                                   district_id,
      (CASE WHEN m.type >= '3'
        THEN left(m.location_id, 9) * pow(10, 3)
       ELSE 0
       END)                                   street_id,
      (CASE WHEN m.type > '3'
        THEN m.location_id
       ELSE 0
       END)                                   community_id,
      m.location_id,
      m.username,
      m.type,
      m.id
    FROM `np_manager` m
    WHERE m.flag = '0' ) as m
    on
      mm.id = m.id
set
  mm.`province_id` = m.`province_id`,
  mm.`city_id` = m.`city_id`,
  mm.`district_id` = m.`district_id`,
  mm.`street_id` = m.`street_id`,
  mm.`community_id` = m.`community_id`;

set_column_by_other_table_column.sql

UPDATE np_manager mm
  INNER JOIN
  (SELECT
     m.`location_id`,
     m.`address`,
     ad.`community_id`,
     ad.`street_id`,
     ad.`district_id`,
     ad.`city_id`,
     ad.`province_id`
   FROM np_manager m
     INNER JOIN
     (SELECT
        pv.province_id,
        ct.city_id,
        dt.district_id,
        st.street_id,
        mt.community_id
      FROM
        np_sys_province pv,
        np_sys_city ct,
        np_sys_district dt,
        np_sys_street st,
        np_sys_community mt
      WHERE
        pv.province_id = ct.province_id
        AND
        ct.city_id = dt.city_id
        AND
        dt.district_id = st.district_id
        AND
        st.street_id = mt.street_id
        AND
        mt.del_flag = '0') ad
       ON m.location_id = ad.community_id
          AND m.location_id > 650000000000
          AND m.flag = '0') AS m
    ON
      mm.location_id = m.location_id
SET
  mm.`province_id`  = m.`province_id`,
  mm.`city_id`      = m.`city_id`,
  mm.`district_id`  = m.`district_id`,
  mm.`street_id`    = m.`street_id`,
  mm.`community_id` = m.`community_id`;