Question

I'm having a bit of trouble setting up a command for this, any tips? (MySQL)

Update table room to set the value of column roomtype equal to the corresponding id value in the table roomtype. This requires a select * from roomtype; to see what the id value is for each roomDesc. For example, in my database

a. 1 | Critical

b. 2 | Private

c. 3 | Semi-Private

I think i need to use a Join but I'm not sure, any tips are appreciated !

Answer 1

From the question it shows that the table

room has attributes (roomtype, .. , ..) and

roomtype has attributes (id, roomDesc).

roomtype column in table `room` is supposed to store the "corresponding" id of roomtype , so it means that the table `room` also has another attribute specifying room description i.e. roomDesc. It is on the the basis of this that we will assign corresponding room ids in table `room`.

So the MySql query for this is:

UPDATE room SET roomtype = (SELECT roomtype.id FROM roomtype INNER JOIN room ON roomtype.roomDesc = room.roomDesc);

OR

UPDATE room SET roomtype = (SELECT roomtype.id FROM roomtype a INNER JOIN room b ON a.roomDesc = b.roomDesc);

OR

UPDATE room SET roomtype = (SELECT roomtype.id FROM roomtype INNER JOIN room USING (roomDesc));

when we don't use any join it is equivalent to an inner join.

Now let us understand the first syntax, then all the following are just equivalent syntax to know different ways of querying.

First,

SELECT roomtype.id FROM roomtype INNER JOIN room ON roomtype.roomDesc = room.roomDesc;

In both the tables where the roomDesc value matches, the entire row consisting of combined attributes of both the tables after the join are returned, (with the help of INNER JOIN)

(roomtype.id, room.roomtype, roomtype.roomDesc, .... ,,..)

from this row only roomtype.id is selected using SELECT roomtype.id ...

now this value is used to set room.roomtype essentially like

UPDATE room SET roomtype = "(the corresponding roomtype.id that was returned from above)";  

NOTE: WHERE clause was not used in this case of UPDATE statement, and we assume that the id corresponding to roomDesc in table roomtype table is one-one i.e. for eeach type of room description only one room id is there.