基于另外两个表更新列值(mysql)

vktxenjb 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(278)

我有三张table，目标是提高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
--------------------------------

我试过这个代码，把它改成不同的变体，但没有用。它更新了所有“普拉提”和“哈他”课程的价格，但没有专门针对“京都房间”。拜托，我需要帮助。

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')

sql mysql

来源：https://stackoverflow.com/questions/51614990/update-column-values-based-on-another-two-tables-mysql

1条答案

按热度按时间

gfttwv5a1#

使用 join :

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';

赞(0）回复(0）举报 2021-06-20

我来回答

基于另外两个表更新列值(mysql)

1条答案

相关问题

热门标签

最新问答