SQL, Delete Records Based On Related Fields In Other Table

Question

I have a SQL table based on hotel data. I have two tables and a bridge table to relate them. I'm still learning so I'm sure some of this is not ideal or has potential risks.

Guest Table

CREATE TABLE Guest 
(
    Guest_ID INT PRIMARY KEY IDENTITY (1, 1),
    GuestName NVARCHAR(60) NOT NULL,
    Street NVARCHAR(50) NOT NULL,
    City NCHAR(30) NOT NULL,
    [State] CHAR(2) NOT NULL,

    CONSTRAINT [State.State] 
        FOREIGN KEY ([State]) REFERENCES [State]([State]),
    Zip CHAR(5) NOT NULL,
    Phone VARCHAR(15) NOT NULL
);

Room Table

CREATE TABLE Room 
(
    Room_ID SMALLINT PRIMARY KEY,
    Room_Type_ID SMALLINT NOT NULL,
    CONSTRAINT Room_Type_ID  
        FOREIGN KEY (Room_Type_ID) REFERENCES Room_Type([Type_ID]),
    Amenity_Type_ID SMALLINT NOT NULL,
    CONSTRAINT Amenity_Type_ID 
        FOREIGN KEY (Amenity_Type_ID) REFERENCES Amenity_Type([Type_ID])
);

Bridge Table (Reservations)

CREATE TABLE Guest_Bridge_Rooms 
(
    Guest_ID INT NOT NULL,
    CONSTRAINT Guest_ID 
        FOREIGN KEY (Guest_ID) REFERENCES Guest(Guest_ID),
    Room_ID SMALLINT NOT NULL,
    CONSTRAINT Room_ID 
        FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID),
    Date_Start DATE NOT NULL,
    Date_End DATE NOT NULL,
    Occ_Adults SMALLINT NOT NULL,
    Occ_Children SMALLINT NOT NULL,
    Price_Total DECIMAL(13,2) NOT NULL
);

Now with these tables, I would like to write a script to DELETE all rows where a reservation (bridged table) has a specific guest NAME by somehow relating the given Guest_ID to its GuestName in the related table. I could simply use Guest_ID but that is not the goal here.

For example something like

DELETE FROM Guest_Bridge_Rooms 
WHERE Guest[ID].GuestName = 'John Doe';

Is there a simple way to do this?

This question was closed as a duplicate to something for SQL Server. I don't see that the question has a tagged database. And it doesn't mention the database in the question itself. @CraigJohnson . . . You should tag with the database you are using. — Gordon Linoff, Sep 20 '21 at 17:51
@GordonLinoff did I not? It's tagged SQL, and I'm using MSSMS which I did not find in the tag list. Otherwise I don't know what you're talking about, and I did not find another topic that matched my question (it's hard to know to ask about subqueries when you don't know a subquery is what you need) — Craig Johnson, Sep 20 '21 at 18:10
@GordonLinoff This wasn't some random question about "something for SQL Server", it was specifically about using a join in a SQL delete, which quickly solves problems like this. — Sergey Kalinichenko, Sep 20 '21 at 18:15

score 0 · Accepted Answer · answered Sep 20 '21 at 17:52

You can use a subquery:

DELETE FROM Guest_Bridge_Rooms
    WHERE Guest_ID = (SELECT g.Guest_Id FROM Guests g WHERE g.GuestName = 'John Doe');

Note: The exact syntax might vary, depending on the database. This also assumes that GuestName is unique in Guests.

SQL, Delete Records Based On Related Fields In Other Table

1 Answers1