List:General Discussion« Previous MessageNext Message »
From:shawn green Date:September 4 2013 4:11am
Subject:Re: How to update MySQL table based on 3 other tables
View as plain text  
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
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