-1

I have two tables as follows - 

Room:
room | room_name | size | index (AI,pk)
  0  |   CR20    |  30  |   1
  1  |   CR30    |  40  |   2

Enroll:
...| room | size | index(AI, pk)
   |  0   |      |    1
   |  1   |      |    2
   |  0   |      |    3
   |  1   |      |    4

I wish to update the table Enroll, where Enroll.room is assigned the respective room_name from table Room. i.e. all the 0's and 1's in field room from Enroll will change to CR20 or CR30 and the size values will be filled in respectively.

What MySQL query would be suitable for this? Any help would be appreciated.

roeygol
  • 4,908
  • 9
  • 51
  • 88
Hasan Peeal
  • 1
  • 2
  • 1
    as you are new here, you should read this first: http://stackoverflow.com/tour - you need to show us what you have tried - stackoverflow isn't for asking us to do things for you, its to help you do things on your own – Jonathan Jan 25 '15 at 17:21
  • http://stackoverflow.com/questions/2114534/mysql-syntax-for-join-update This link should help. – SkelDave Jan 25 '15 at 17:24
  • Yes I am new here. Okay in that case, I have tried: Update enroll INNER JOIN room ON enroll.room = room.room SET name = room.room_name. But it is showing error with this query. I want some suggestions on what I can use, info on UPDATE and JOIN is difficult to find/use. – Hasan Peeal Jan 25 '15 at 17:24
  • Why would you store the redundant information in `Enroll`? You can just get it using a `join` when you need it. – Gordon Linoff Jan 25 '15 at 17:26
  • That is a very good point. But am using `mysqli_fetch_assoc` in php to retrieve all the values from the Enroll table which has a few more fields. That is why I was thinking of swapping it out. I'll try resolving with `join` as you said. – Hasan Peeal Jan 25 '15 at 17:43

1 Answers1

1

You need update from join syntax

UPDATE Enroll a
JOIN Room b
   ON a.room = b.room
SET a.size = b.size,
a.name = b.room_name
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172