Как обновить значение столбца на основе последней вставленной записи в другой таблице

#sql-server

Вопрос:

Необходимо обновить AttentionStatus / UpdatedDate для PatientId в таблице 1, когда новая запись вставляется в таблицу 2, также необходимо переместить AttentionStatus ( CurrentStatus ) в PreviousStatus . Значение обновления основано на последней вставке в таблице 2.

Код:

 create table Table1
(
    Id int, 
    PatientName varchar(200), 
    AttentionStatus varchar(20), 
    PreviousStatus varchar(20), 
    UpdatedDate datetime
)

create table Table2
(
    Id int identity(1,1), 
    PatientId int, 
    Attention varchar(20), 
    Date datetime
)

insert table1 select 101, 'Patient1', 'Yellow', 'Red', '2021-05-28 03:22:06.650'
insert table1 select 102, 'Patient2', 'Red', 'Yellow', '2021-05-26 14:29:52.620'

insert table2 select 1, 101, 'Yellow', '2021-05-28 03:22:06.650'
insert table2 select 2, 101, 'Red', '2021-05-28 03:14:56.010'
insert table2 select 3, 102, 'Red', '2021-05-26 14:29:52.620'
insert table2 select 4, 102, 'Yellow', '2021-05-25 10:27:37.107'
insert table2 select 5, 102, 'Green', '2021-05-24 06:13:18.430'

Id  PatientName AttentionStatus PreviousStatus  UpdatedDate
101 Patient1    Yellow          Red             2021-05-28 03:22:06.650
102 Patient2    Red             Yellow          2021-05-26 14:29:52.620

Ввод:

 insert table2 select 102, 'Green', '2021-05-29 10:24:25.007'

Ожидаемый результат:

 Id    PatientName     AttentionStatus   PreviousStatus     UpdatedDate
101   Patient1        Yellow            Red                2021-05-28 03:22:06.650
102   Patient2        Green             Red                2021-05-29 10:24:25.007

Это моя попытка задать вопрос:

 update table1 
set AttentionStatus = t2.attention, 
    previousstatus = t1.attentionstatus, 
    UpdatedDate = t2.date 
from table1 t1 
inner join 
    (select patientid, attention, date 
     from table2  
     group by patientid, attention 
     having date = max(Date)) t2 on t1.id = t2.PatientId
where t1.id = 102

1. пожалуйста, покажите свой запрос на попытку

2. Я не понимаю: если update table1 запрос выполняется в одном пакете , то вы должны знать, какие строки они содержат having... , и если он выполняется в триггере, вы должны использовать inserted таблицу. Так как же ты этим управляешь update ?

Ответ №1:

Вам не нужны поля AttentionStatus и PreviousStatus (из-за нормализации базы данных.

см. раздел DBFIDDLE

Код:

 ALTER TABLE table1 DROP COLUMN AttentionStatus;
ALTER TABLE table1 DROP COLUMN PreviousStatus;

CREATE VIEW vw_table1 AS (
   
   SELECT 
      Id,
      PatientName, 
      x.Attention as AttentionStatus,
      y.Attention as PreviousStatus,
      UpdatedDate
   FROM table1
   LEFT JOIN 
    (
      SELECT  PatientID,
         ROW_NUMBER() OVER (PARTITION By PatientID ORDER By DATE DESC) as row,
         Attention
      FROM Table2
    ) x ON x.PatientID=table1.Id and x.row=1
   LEFT JOIN 
    (
      SELECT PatientID,
         ROW_NUMBER() OVER (PARTITION By PatientID ORDER By DATE DESC) as row,
         Attention
      FROM Table2
    ) y ON y.PatientId=table1.Id AND y.row=2
    
);

Это создает представление с результатами, такими как:

ID	Имя пациента	Состояние внимания	Предыдущее состояние	Дата обновления
101	Пациент 1	Желтый	Красный	2021-05-28 03:22:06.650
102	Пациент 2	Зеленый	Красный	2021-05-26 14:29:52.620