How to update a value of a particular row which satisfies a condition inside the SELECT statement in SQL server

Question

i have tables like "Bed" and "PatientRecord". I want to allocate an empty bed to a patient (i.e bID from Bed table with status ="Available") then change that bID status to "Occupied". Please help with this. The following is my current query but i am unsure how to proceed.

update Patient_Record 
set b_ID = (select top 1 b_ID from bed where Status_Avai_Occ = 'Available')
/*i want to change the status of this b_ID to "Occupied"*/
where Admission_Type = 'In Patient' 

Answer 1

SQL Server doesn't allow you to update more than one table in a single UPDATE statement, however you can simply wrap the two updates in a single transaction, as follows:

DECLARE @b_ID INT
SELECT TOP 1 @b_ID = b_ID FROM bed WHERE Status_Avai_Occ = 'Available'

BEGIN TRAN Update_Patient

UPDATE  Patient_Record 
  SET   b_ID = @b_ID
  WHERE Admission_Type = 'In Patient' 

UPDATE  bed
  SET   Status_Avai_Occ = 'Occupied'
  WHERE (b_ID = @b_ID)

COMMIT TRAN Update_Patient

Answer 2

I think the reason you are finding this difficult is because your design is flawed, but there isn't enough information to correct that. I'm not sure which patient you are trying to give a bed to, in below i've used the @PatientIdToGiveBed to specify that as in your example you will give all 'In Patient' the same bed. It's possible you will need to modify this to select the first 'In Patient' in the same way that the bed is selected.

The transaction needs to have an isolation level of REPEATABLE READ which will prevent two transactions from assigning the same bed, if you used the default READ COMMITTED, two transactions could select the same bed. This way a READ LOCK is held on the selected bed until the transaction is complete.

You can put the entire statement below into SSMS and run to test it.

DECLARE @Bed TABLE  (
    b_ID INT,
    Status_Avai_Occ VARCHAR(20)
)

DECLARE @Patient_Record TABLE (
    Id INT, 
    Name VARCHAR(10),
    b_ID INT
)

INSERT INTO @Bed VALUES (1, 'Available') 
INSERT INTO @Bed VALUES (2, 'Available') 
INSERT INTO @Bed VALUES (3, 'Available') 

INSERT INTO @Patient_Record VALUES (1, 'Adam', NULL) 
INSERT INTO @Patient_Record VALUES (2, 'Ben', NULL) 
INSERT INTO @Patient_Record VALUES (3, 'Charles', NULL) 

DECLARE @PatientIdToGiveBed INT = 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRANSACTION 
    DECLARE @Available_BID INT
    SELECT TOP 1 @Available_BID = b_ID FROM @BED WHERE Status_Avai_Occ = 'Available' ORDER BY b_ID

    IF @Available_BID IS NULL 
    BEGIN
        Print 'No Beds Available'
        COMMIT 
        RETURN 
    END 

    UPDATE @Patient_Record SET b_ID = @Available_BID WHERE Id = @PatientIdToGiveBed 
    UPDATE @Bed SET Status_Avai_Occ = 'Occupied' WHERE b_ID = @Available_BID 
COMMIT 

SELECT * FROM @Patient_Record
SELECT * FROM @Bed