Hello Neil,
On 8/24/2013 5:21 AM, Neil Tompkins wrote:
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
Have you tried the multi-table syntax of the UPDATE command?
http://dev.mysql.com/doc/refman/5.6/en/update.html
UPDATE City c INNER JOIN HotelRegion h ON h.HotelID = c.CityID
SET City.RegionID = h.RegionID
WHERE ...
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
