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?

Use a `LEFT JOIN` and check to see if the right side (`roomBooking`) `IS NULL` — Siyual, Nov 26 '14 at 20:35

score 2 · Answer 1 · answered Nov 26 '14 at 20:36

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

answered Nov 26 '14 at 20:36

Vinnie

3,889
1
26
29

Gordon Linoff · Accepted Answer · 2014-11-26T21:10:52.793

2

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.

edited Nov 26 '14 at 21:10

answered Nov 26 '14 at 20:37

Gordon Linoff

1,242,037
58
646
786

I want to update disabledAccess to yes (SET disabledAccess = 1) but when I run this, I get a syntax error message – Laura Berry Nov 26 '14 at 21:27
This SQL worked for me though :) UPDATE room LEFT JOIN roomBooking ON room.rCode = roomBooking.roomCode SET disabledAccess = 1 WHERE roomBooking.roomCode IS NULL; – Laura Berry Nov 26 '14 at 21:38

score 1 · Answer 3 · answered Nov 26 '14 at 20:36

1

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

answered Nov 26 '14 at 20:36

Mat Richardson

3,576
4
31
56

SQL UPDATE statement

3 Answers3