CODEWITHSUNDEEP
sqlsql-servert-sql
Covfefe
Covfefe

Reputation: 11

Update SQL table with most recent value

I am building a database (SQL Server 2008) for an application to track server backups. Each day we get a report showing the status of last night's backups.

When a backup is successful (backup_ok = 'OK'), then I have an UPDATE query which sets the last_recovery_date to the backup_date. Where I am struggling is when a backup is not successful/NOT OK.

In the example below, the last_recovery_date for VM001 should be 2018-10-23. What should the T-SQL code for this be?

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

Upvotes: 1

Views: 67

Answers (2)

Mahesh.K
Mahesh.K

Reputation: 901

I guess this query will be helpfull for you .Once make a try 

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need update statement, so you can use 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';

Upvotes: 3

Related Questions