你可以的 lef join 这个 new_table 三次，使用 coalesce() 优先顺序：

update address_table a
left join new_table n1 on n1.house_id  = a.addres_id
left join new_table n2 on n2.school_id = a.addres_id
left join new_table n2 on n3.office_id = a.addres_id
set a.state = coalesce(n1.state, n2.state, n3.state)
where coalesce(n1.house_id, n2.school_id, n3.office_id) is not null

这个 where 子句确保至少有一个联接成功。

尝试此查询：

SELECT NewTable.State FROM NewTable WHERE 
NewTable.School IN (SELECT AddressTable.AdressId FROM AddressTable) 
OR NewTable.Office IN (SELECT AddressTable.AdressId FROM AddressTable) 
OR NewTable.House IN (SELECT AddressTable.AdressId FROM AddressTable);