CODEWITHSUNDEEP
mysqlsql
Hansen Christian
Hansen Christian

Reputation: 31

Update column values based on another two tables (MySQL)

I have three tables, the goal is to increase the ClassPrice by 10% for all 'Pilates' and 'Hatha' classes which are held in 'Kyoto Room'.

**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
--------------------------------

I've tried this code and changing it into different variations but it doesn't work. It updated the prices for all 'Pilates' and 'Hatha' classes but not specifically for only 'Kyoto Room'. Please i need help.

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

Upvotes: 2

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 1

Related Questions