List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:August 24 2013 9:21am
Subject:How to update MySQL table based on 3 other tables
View as plain text  
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

Thread
How to update MySQL table based on 3 other tablesNeil Tompkins24 Aug
  • Re: How to update MySQL table based on 3 other tablesshawn green4 Sep