Geek asks and answers

How to update the value of a specific row that satisfies a condition inside a SELECT statement on an SQL server

I have tables like Bed and PatientRecord. I want to highlight an empty bed for the patient (i.e. BID from the Bed table with status = "Available"), then change the bID status to "Busy". Please help with this. The following is my current request, but I'm not sure how to do this.

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'
+5
source share
6 answers

This will update all PatientRecords with Admission_Type = In Patient

I'm not sure I fully understand your structure, but you can do this:

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

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

UPDATE bed
SET Status_Avai_occ = 'Occupied'
WHERE b_ID = @AvailableBid

, Patient_records Admission_Type In Patient. , .. .

http://sqlfiddle.com/#!6/fa26d/9

DECLARE @AvailableBID INT
DECLARE @AvailablePatientRecord INT

SELECT TOP 1 @AvailableBID = b_ID FROM bed WHERE Status_Avai_Occ = 'Available'
SELECT TOP 1 @AvailablePatientRecord  = PatientRecordId FROM Patient_Record WHERE Admission_Type = 'In Patient'

UPDATE Patient_Record 
SET b_ID = @AvailableBID
WHERE PatientRecordId = @AvailablePatientRecord

UPDATE bed
SET Status_Avai_occ = 'Occupied'
WHERE b_ID = @AvailableBid

: , PatientRecordId Patient_Record

0

, , , , , . , , @PatientIdToGiveBed, , , , " " . , , "In Patient" , .

REPEATABLE READ, , READ COMMITTED, . , a READ LOCK .

SSMS .

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
+3

SQL Server UPDATE, :

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
+2
1. set variable "openBed" to available bed id
2. update bed with bed id = openBed to 'Occupied'
3. update patient_record set bed id = openBed where admission_type = 'in patient'

. 3- In Patient Patient_Record . , , . .

+2
source

If you really want to run this through a set of patients at the same time, you need to map the beds first and then assign them, since in SQL Server there is no way to update two tables at once so that your set-based select will give different bed_IDs each time.

-- Set up some temp tables to hold data
DECLARE @availBeds table (num int identity(1,1), b_ID int)
DECLARE @inPatients table (num int identity(1,1), p_ID int)
DECLARE @patientBeds table (p_ID int, b_ID int)

-- Populate table of available beds only, giving them a unique incremental ID
INSERT INTO @availBeds SELECT b_ID FROM Bed WHERE status_avai_occ = 'Available'

-- Populate table of in patients without assigned beds only, giving them a unique incremental ID
INSERT INTO @inPatients SELECT p_ID FROM Patient_Record WHERE admission_type = 'In Patient' AND b_ID IS NULL

-- Join the two tables above based on their incremental ID's,
-- effectively matching up available beds with patients
INSERT INTO @patientBeds
    SELECT p.p_ID, b.b_ID FROM @inPatients p
    INNER JOIN @availBeds b ON b.num = p.num

-- Update Patient_Record with the new bed ID's
UPDATE p
SET p.b_ID = b.b_ID
FROM Patient_Record p
INNER JOIN @patientBeds b ON p.p_ID = b.p_ID

-- Set the Beds to Occupied
UPDATE Bed SET status_avai_occ = 'Occupied' WHERE b_ID IN (SELECT b_ID FROM @patientBeds)

Demo

+2
source

  • You cannot update 2 tables in one query (you need to use transactions)

  • you can try something like this

Begin Tran

update Patient_Record 
set b_ID = b.b_ID from bed b where b.Status_Avai_Occ = 'Available'
and Patient_Record.Admission_Type = 'In Patient'

Update bed set Status_Avai_Occ='Occupied' where b_ID in (select b_ID from Patient_Record where Patient_Record.Admission_Type = 'In Patient')
Commit Tran
+2
source

More articles:


All Articles