mysql - 根据另外两个表更新列值(MySQL)

Question

我有三张 table ，目标是将在“Kyoto Room”举办的所有“Pilates”和“Hatha”类(class)的类(class)价格提高 10%。

**YogaRooms**

RoomNum |  RoomName     | RoomCapacity | Branch       | CostPerHour
--------------------------------------------------------------------
  001   | Surya Room    |      20      | Sydney       |    100.00
  002   | Chandra Room  |      10      | North Sydney |     50.00
  003   | Ashtanga Room |      25      | North Sydney |    150.00
  004   | Yin Room      |      15      | Sydney       |     70.00
  005   | Kyoto Room    |      30      | Sydney       |    130.00
--------------------------------------------------------------------

**YogaTimeTable**

YogaId | StartTime | Day   | RoomNum | Instructor | Duration
-------------------------------------------------------------
  DRU  |   10.30   |  Wed  |   001   |    Mark    |  60.00 
  DRU  |   17.00   |  Tue  |   002   |    Julie   |  90.00
  HAT  |   18.30   |  Mon  |   004   |    Cora    |  60.00
  HAT  |    7.30   |  Tue  |   004   |    Mark    |  90.00
  PIL  |   17.00   |  Thu  |   004   |    Julie   |  60.00
  PIL  |   18.30   |  Wed  |   003   |    Nicky   | 120.00
  SUN  |    7.30   |  Mon  |   003   |    Mark    |  60.00
  HAT  |   18.30   |  Mon  |   005   |    Cora    |  60.00
  HAT  |    7.30   |  Tue  |   005   |    Mark    |  90.00
  PIL  |   17.00   |  Thu  |   005   |    Julie   |  60.00
-------------------------------------------------------------

**YogaTypes**

YogaId | YogaName | ClassPrice
-------------------------------
  BHA  |  Bhakti  |   17.00
  DRU  |   Dru    |   18.50
  HAT  |  Hatha   |   20.00
  PIL  |  Pilates |   15.50
  SUN  |  Sunrise |   15.00
--------------------------------

我试过此代码并将其更改为不同的变体，但它不起作用。它更新了所有“普拉提”和“Hatha”类(class)的价格，但不是专门针对“Kyoto Room”。我需要帮助。

UPDATE YogaTypes
SET ClassPrice = ClassPrice * 1.1
WHERE YogaId IN (select T.YogaId
                 from YogaRooms R, YogaTimeTable T, YogaTypes YT
                 where R.RoomNum = T.RoomNum
                 and YT.YogaId = T.YogaId
                 and R.RoomNum = '005')

Answer 1

使用加入:

UPDATE YogaTypes yt JOIN
       YogaTimeTable ytt
       ON yt.YogaId = ytt.YogaId
    SET yt.ClassPrice = yt.ClassPrice * 1.1
WHERE yt.YogaName in ('Pilates', 'Hatha') AND
      ytt.RoomNum = '005';