How to remove duplicate rows in SQL Server self join

Question

I am trying to modify one column value on the basis of another row in same table, so I am using self join, I am getting the expect changes, but its selecting both rows (original and modified one), I don't want to select original row, if any of its column is being modified, it should only select modified row, if its values are modified, otherwise it should select original row. I created a similar schema to elaborate the issue:

    create table tbl
    (
        id int not null,    
        status int not null,
        name varchar null,
        subject varchar null,
        result varchar null
    );


    /* Create few records in this table */
INSERT INTO tbl VALUES(1, 1, 'A', 'sub1', 'Pending');
INSERT INTO tbl VALUES(2, 2, 'A', 'all', 'Passed');
INSERT INTO tbl VALUES(3, 1, 'B', 'sub1', 'Pending');
INSERT INTO tbl VALUES(4, 3, 'B', 'sub2', 'Failed');
INSERT INTO tbl VALUES(5, 3, 'C', 'sub1', 'Failed');
INSERT INTO tbl VALUES(6, 2, 'D', 'sub1', 'Passed');
INSERT INTO tbl VALUES(7, 1, 'E', 'sub1', 'Pending');
COMMIT;

    SELECT distinct t1.id, t1.status, t1.name, t1.subject,
    CASE 
        WHEN t1.status = 1 and t2.subject = 'all' and t2.status = 2 THEN 'Passed'
        WHEN t1.status = 1 THEN 'Pending'
        WHEN t1.status = 2 THEN 'Passed'
        WHEN t1.status = 3 THEN 'Failed'
    END AS 'result'
    FROM tbl t1 join tbl t2 on t1.name = t2.name

----- Result ----------------------------

1|1|A|sub1|Pending
1|1|A|sub1|Passed
2|2|A|all|Passed
3|1|B|sub1|Pending
4|3|B|sub2|Failed
5|3|C|sub1|Failed
6|2|D|sub1|Passed
7|1|E|sub1|Pending

Here, the row with ID: 1 is getting duplicated, I don't want the first row anymore since I modified its Result column value to Passed on basis of second row in the original table subject: all (ID: 2).

------ Expected Result-----------------------

1|1|A|sub1|Passed
2|2|A|all|Passed
3|1|B|sub1|Pending
4|3|B|sub2|Failed
5|3|C|sub1|Failed
6|2|D|sub1|Passed
7|1|E|sub1|Pending

Answer 1

The issue with your sample is your join produces one row where subject = sub1 on both sides so your CASE statement does not catch it. Easiest way to modify your query for the desired result is to eliminate that scenario by expanding your join conditions to exclude like statuses.

SELECT distinct t1.id, t1.status, t1.name, t1.subject,
CASE 
    WHEN t1.status = 1 and t2.subject = 'all' and t2.status = 2 THEN 'Passed'
    WHEN t1.status = 1 THEN 'Pending'
    WHEN t1.status = 2 THEN 'Passed'
    WHEN t1.status = 3 THEN 'Failed'
END AS 'result'
FROM tbl t1 left join tbl t2 on t1.name = t2.name AND t1.status <> t2.status