如何更新满足SQL server中SELECT语句内部条件的特定行的值

Question

我有"Bed"one_answers"PatientRecord"这样的表格。我想为患者分配一张空床(即床表中状态为"可用"的bID(，然后将该bID状态更改为"已占用"。请帮忙。以下是我目前的问题，但我不确定如何继续。

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

我认为你发现这很困难的原因是因为你的设计有缺陷，但没有足够的信息来纠正。我不确定你想给哪个病人一张床，在下面我用了@PatientIdToGiveBed来指定，就像你的例子一样，你会给所有的"住院病人"一张床。您可能需要对此进行修改，以便以与选择床位相同的方式选择第一个"住院患者"。

事务需要具有REPEATABLE READ的隔离级别，这将防止两个事务分配同一张床，如果使用默认的READ COMMITTED，两个事务可以选择同一张床位。通过这种方式，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

Answer 2

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

Answer 3

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'

注意：3rd语句每次都会将Patient_Record表中的每个In Patient记录更新为相同的床id。换言之，每次新患者入院时，每个患者都会被重新分配到下一张可用的床位。那将是一张非常拥挤的床。

Answer 4

如果您真的想一次在一组患者中运行此操作，您需要首先匹配床位，然后分配它们，因为SQL Server中没有办法一次更新两个表，因此基于集合的选择每次都会产生不同的bed_ID。

-- 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)

演示

Answer 5

1. 您不能在单个查询中更新2个表(您需要使用事务(

2. 你可以试试这种

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

Answer 6

这将使用Admission_Type=In Patient 更新所有PatientRecord

我不确定我是否完全理解你的结构，但你可以这样做：

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

请注意，这仍将使用Admission_Type In Patient更新所有Patient_records。您将需要应用一个额外的过滤器，即行号。

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

注意：您必须确保PatientRecord表中有PatientRecordId