SQL update table via join?

Question

I have the following database and wish to update the room table. The table room lists a room type that is either single, double or king, the price is the price of each room per night and the name in Hotel is the name of the hotel.

What I need to do is change all double rooms to king rooms which are in the Scotty Hotel and also increase their price by 10%.

I know how to update the price and the type when it is in a single table, but it seems here I need to join hotel and room on HNO and thus update. Nested query maybe?

create table Hotel  (
   HNo char(4),
   Name varchar(20)   not null,
   Address varchar(50),
   Constraint PK_Hotel Primary Key (HNo)
);

create table Room  (
   RNo char(4),
   HNo char(4),
   Type char(6) not null,
   Price decimal (7,2),
   Constraint PK_Room Primary Key (HNo, RNo),
   Constraint FK_Room Foreign Key (HNo)
   references Hotel (HNo)
);


create table Guest  (
   GNo char(4),
   Name varchar(20) not null,
   Address varchar(50),
   Constraint PK_Guest Primary Key (GNo)

);

create table Booking   (
   HNo char(4),
   GNo char(4),
   DateFrom date,
   DateTo date,
   RNo char(4),
   Constraint PK_Booking Primary Key (HNo, GNo, DateFrom),
   Constraint FK_Booking Foreign Key (GNo)
   references Guest (GNo),
   Constraint FK_Booking_room Foreign Key (HNo, RNo)
   references Room (HNo, RNo),
   Constraint FK_Booking_hotel Foreign Key (HNo)
   references Hotel (HNo)
);

I have two main questions:

1. Is it possible to do an update to a table, that requires a join?

2. I want to list guests via a view. Can I create a view which contains the hotel name and number of distinct guests that have ever stayed in the hotel?

Answer 1

First, it is a good idea to do a select to ensure your search/filter criteria are correct:

SELECT
  h.Name,
  r.RNo,
  r.Type,
  r.Price

FROM
  room r

  INNER JOIN hotel h
  on h.hno = r.hno

WHERE
  h.name = 'Scotty Hotel'
  and
  r.type = 'Double' ;

If this targets the correct rows, then have at 'em with an update query using the same filter criteria, as follows.

Hint, it pays to copy your entire SELECT query and then edit it to form your new UPDATE query. eg for mySql, replace FROM with UPDATE (and leave the contents of the original FROM clause to form your new UPDATE clause), leave the WHERE in place, and use the contents of your SELECT list from the original query as the basis on which to form your new SET clause (immediately prior to your WHERE clause).

This code is for mySql:

UPDATE room r 

  INNER JOIN hotel h
  on h.hno = r.hno

SET
  r.type = 'King',
  r.price = r.price * 1.1

WHERE
  h.name = 'Scotty Hotel'
  and
  r.type = 'Double' ;

This code is for MS SQL Server:

UPDATE r

SET
  r.type = 'King',
  r.price = r.price * 1.1

FROM
  room r

  INNER JOIN hotel h
  on h.hno = r.hno

WHERE
  h.name = 'Scotty Hotel'
  and
  r.type = 'Double' ;

Answer 2

Why does not this work?

UPDATE ROOM A SET TYPE='King', A.PRICE=A.PRICE*1.1 
WHERE A.TYPE ='DOUBLE' AND 
A.HNO IN (SELECT HNO FROM HOTEL WHERE NAME='Scotty')

Hope that this works. This assumes that the hotel name Scotty is unique, of course