使用最新值更新SQL表

Question

我正在为一个应用程序构建一个数据库(SQL Server 2008)来跟踪服务器备份。每天我们都会收到一份报告，显示昨晚备份的状态。

当备份成功(backup_ok = 'OK')时，我有一个UPDATE查询，它将last_recovery_date设置为backup_date。当备份不成功/不正常时，我正在苦苦挣扎。

在下面的示例中，VM001的last_recovery_date应为2018-10-23。这个T-SQL代码应该是什么？

server | backup_date | backup_ok | last_recovery_date
-------+-------------+-----------+-------------------
VM001  | 2018-10-24  | NOT OK    |
VM002  | 2018-10-24  | OK        | 2018-10-24
VM001  | 2018-10-23  | OK        | 2018-10-23
VM002  | 2018-10-23  | OK        | 2018-10-23

Answer 1

您需要使用update语句，因此可以使用subquery：

update t
      set t.last_recovery_date = (select max(t1.last_recovery_date) 
                                  from table t1 
                                  where t.server = t1.server and t1.backup_ok = 'OK'
                                 )
from table t
where backup_ok = 'NOT OK';

Answer 2

我想这个查询会对你很有帮助的，.Once试一试

SELECT * INTO #TEST FROM (
SELECT'VM001' [server] ,'2018-10-24'backup_date ,'NOT OK'backup_ok ,NULL last_recovery_date
UNION ALL SELECT'VM002','2018-10-24','OK    ','2018-10-24'
UNION ALL SELECT'VM001','2018-10-23','OK    ','2018-10-23'
UNION ALL SELECT'VM002','2018-10-23','OK    ','2018-10-23'
)AS A


SELECT * FROM #TEST

UPDATE T SET T.last_recovery_date=A.backup_date  
FROM #TEST T
CROSS APPLY ( SELECT MAX(backup_date)AS backup_date 
              FROM #TEST T1 WHERE T.[SERVER]=T1.[SERVER] 
              AND T1.backup_ok='OK'
             ) AS A
WHERE T.backup_ok='NOT OK'

SELECT * FROM #TEST