SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Table1
    ([Name] varchar(16), [Job] varchar(16), [Status] varchar(16))
;
    
INSERT INTO Table1
    ([Name], [Job], [Status])
VALUES
    ('Test1', 'Completed', 'Pending'),
    ('Test1', NULL, 'Pending'),
    ('Test2', 'Completed', 'Pending'),
    ('Test2', 'Completed', 'Pending'),
    ('Test2', 'Completed', 'Pending')
; 
5 rows affected

UPDATE Table1
SET Status = 'Completed'
WHERE
    Job = 'Completed' AND
    NOT EXISTS (SELECT 1 FROM Table1 t2
                WHERE Table1.Name = t2.Name AND (t2.Job <> 'Completed' OR t2.Job IS NULL));
3 rows affected

SELECT * FROM Table1


Name
Job
Status


Test1
Completed
Pending

Test1
null
Pending

Test2
Completed
Completed

Test2
Completed
Completed

Test2
Completed
Completed

Name	Job	Status
Test1	Completed	Pending
Test1	null	Pending
Test2	Completed	Completed
Test2	Completed	Completed
Test2	Completed	Completed