I have the following four MySQL tables Region RegionId
City CityId RegionId Hotel HotelId CityId HotelRegion HotelId RegionId I'm struggling to write a UPDATE statement to update the City table's RegionId field from data in the HotelRegion table. Basically how can I update the City table with the correct RegionId where the HotelId in the HotelRegion table matches the City table's CityId. This is my UPDATE statement at the moment UPDATE City cSET c.RegionId = (SELECT DISTINCT(HotelRegion.RegionId) FROM HotelRegion INNER JOIN Hotel ON Hotel.HotelID = HotelRegion.HotelIDINNER JOIN City ON City.CityId = Hotel.CityIdWHERE City.CityId = 1233)WHERE c.CityId = 1233
