SQL Server query to update multiple rows with same name

Question

Still playing around with tables and queries but need some help with a SQL Server query.

I have a table that looks like this.

VMName   | DiskType   | Status      | Result
---------+------------+-------------+---------
TestVM1  |  OS        | Completed   | 
TestVM1  |  Data      | Completed   | 
TestVM2  |  OS        | Failed      | 
TestVM2  |  Data      | Completed   | 
TestVM2  |  Data      | Completed   | 

In the results column, I'd like to have it say "Completed" if both the OS and data disks have a status of completed.

If however, the OS disk shows failed, then the results column should show failed for all the disks of that VM.

Like this.

VMName   | DiskType   | Status      | Result
---------+------------+-------------+---------
TestVM1  |  OS        | Completed   | Completed
TestVM1  |  Data      | Completed   | Completed
TestVM2  |  OS        | Failed      | Failed
TestVM2  |  Data      | Completed   | Failed
TestVM2  |  Data      | Completed   | Failed

I tried cobbling together a query, but not sure what I'm doing wrong as it doesn't do anything.

UPDATE working_table t1
SET Result = 'Failed'
WHERE disktype = 'OS' AND 
      status = 'Failed' AND
      NOT EXISTS (SELECT 1 FROM working_table t2 WHERE t1.VMname = t2.VMname);

Answer 1

Seems it only needs an EXISTS

UPDATE t1
SET Result = 'Failed'
FROM working_table t1
WHERE EXISTS (
      SELECT 1 
      FROM working_table t2 
      WHERE t2.VMname = t1.VMname
        AND t2.disktype = 'OS' 
        AND t2.[Status] = 'Failed'
)
AND (Result IS NULL OR Result <> 'Failed'); -- optional criteria for only updating what is needed

Or if you want to update all of the Result, not just those failed :

UPDATE t1
SET Result = CASE
             WHEN EXISTS (SELECT 1 FROM working_table t2 WHERE t2.VMname = t1.VMname AND t2.disktype = 'OS' AND t2.[Status] = 'Failed') 
             THEN 'Failed' 
             ELSE t1.[Status] 
             END
FROM working_table t1;

Answer 2

Please try

create table  working_table
(
VMName varchar(30),
DiskType  varchar(64),
[Status] varchar(30),
Result varchar(64)
)

insert into working_table values
('TestVM1','OS','Completed',NULL),
('TestVM1','Data','Completed',NULL),
('TestVM2','OS','Failed',NULL),
('TestVM2','Data','Completed',NULL),
('TestVM2','Data','Completed',NULL)

;WITH CTE AS
(
SELECT 
*,
SUM(CASE [Status] WHEN 'Failed' THEN 1 ELSE 0 END) OVER (partition by VMName) as v_flag  
FROM working_table
)
UPDATE CTE SET Result=CASE WHEN  v_flag >0 THEN 'Failed' ELSE 'Completed' END

--Query
SELECT * FROM working_table

--Output
/*
VMName                         DiskType                                                         Status                         Result
------------------------------ ---------------------------------------------------------------- ------------------------------ ----------------------------------------------------------------
TestVM1                        OS                                                               Completed                      Completed
TestVM1                        Data                                                             Completed                      Completed
TestVM2                        OS                                                               Failed                         Failed
TestVM2                        Data                                                             Completed                      Failed
TestVM2                        Data                                                             Completed                      Failed
*/