title: How to remove duplicate rows in SQL Server self join
body: <p>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:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>----- Result ---------------------------- </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here, the row with <code>ID: 1</code> is getting duplicated, I don't want the first row anymore since I modified its <code>Result</code> column value to <code>Passed</code> on basis of second row in the original table <code>subject: all (ID: 2)</code>.</p>
  
  <p>------ Expected Result-----------------------</p>
  
  <pre><code>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
  </code></pre>