SQL UPDATE statement

Question

I have two tables - room and roomBooking which include;

                       room
rCode | rName | building | capacity | disabledAccess
Text  | Text  |   Text   |  Number  |     Yes/No

                                roomBooking
bookingID | roomCode | moduleCode | dayReq | timeReq | semester | classSize
  Number  |   Text   |    Text    |  Text  | Day/Time|  Number  |  Number

I want to write an UPDATE statement to update the rooms that are not booked at any time - the results are shown in the SELECT query below

 SELECT rCode, disabledAccess
 FROM room
 LEFT JOIN roomBooking
 ON room.rCode = roomBooking.roomCode
 WHERE roombooking.roomCode IS NULL;

How do I change this into an UPDATE statement?

Answer 1

You can go down this path, but you need a left join and then a comparison for determining which rows don't match. You don't need an exists clause in this case:

SELECT r.rCode, r.disabledAccess
FROM room as r LEFT JOIN
     roomBooking as rb
     ON r.rCode = rb.roomCode
WHERE rb.roomCode IS NULL;

EDIT:

MS Access accepts join syntax for update:

UPDATE room
    SET <foo> = <bar>
    FROM room LEFT JOIN
         roomBooking
         ON room.rCode = roomBooking.roomCode
    WHERE roomBooking.roomCode IS NULL;

Is it not clear what columns you want to update.

Answer 2

Use a LEFT JOIN and return only rooms that don't meet the join criteria.

SELECT rCode, disabledAccess
 FROM room
 LEFT JOIN roomBooking
 ON room.rCode = roomBooking.roomCode
 WHERE roomBooking.roomCode is NULL

Answer 3

You should use a left join and test for NULL to show rows with no matches.

SELECT rCode, disabledAccess
FROM room
LEFT JOIN roomBooking
ON room.Code = roomBooking.roomCode
WHERE roombooking.roomCode IS NULL