如何基于3个其他表更新MySQL表(How to update MySQL table based on 3 other tables)

编程入门行业动态更新时间:2024-10-23 16:29:16

我有以下四个MySQL表

Region RegionId City CityId RegionId Hotel HotelId CityId HotelRegion HotelId RegionId

我正在努力编写一个UPDATE语句来从HotelRegion表中的数据更新City表的RegionId字段。

基本上我如何使用正确的RegionId更新City表，其中HotelRegion表中的HotelId与City表的CityId匹配。

这是我目前的UPDATE声明

UPDATE City c SET c.RegionId = (SELECT DISTINCT(HotelRegion.RegionId) FROM HotelRegion INNER JOIN Hotel ON Hotel.HotelID = HotelRegion.HotelID INNER JOIN City ON City.CityId = Hotel.CityId WHERE City.CityId = 1233) WHERE c.CityId = 1233

我得到的错误消息是您不能在FROM子句中为更新指定目标表'c'。如果您需要更多信息来帮助回答我的问题，请与我们联系

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

The error message I get is You can't specify target table 'c' for update in FROM clause. Please let me know if you need any more information to help answer my question

最满意答案

必须测试后，我设法得到以下更新查询以正常工作

UPDATE City INNER JOIN Hotel ON City.CityId = Hotel.CityId INNER JOIN HotelRegion ON Hotel.HotelId = HotelRegion.HotelId SET City.RegionId = HotelRegion.RegionId WHERE City.CityId = 1223 AND Hotel.CityId = 1223

After must testing, I managed to get the following update query out to work correctly

更多推荐

本文发布于:2023-04-29 01:24:00，感谢您对本站的认可！

本文链接:https://www.elefans.com/category/jswz/34/1334558.html