title: SQL Server 2012: Update table with inner join after sorted
body: <p>I am using SQL Server 2012. I have a table called <code>table1</code> like below:</p>
  
  <pre><code>Id col1 col2 col3 Name
  1   a    b   abc  null
  2   b    c   mno  null
  </code></pre>
  
  <p>And I have another table <code>table2</code>, like below:</p>
  
  <pre><code>Id col1 col2 col3 Name
  1  %    %    abc  Name1
  2  a    %    abc  Name2
  3  %    b    abc  Name3
  4  a    b    abc  Name4
  </code></pre>
  
  <p>I have to update <code>Name</code> column in <code>Table1</code> From <code>Name</code> column in <code>Table2</code> based on columns: <code>col1</code>, <code>col2</code> and <code>col3</code>.<br>
  The <code>Id = 1</code> in the <code>table1</code> finds all 4 matches in the table because I am using like operator in <code>col1</code> and <code>col2</code> to compare(why I am using like is if it didn't find exact match it should accept <code>%</code> as a match).<br>
  Now my problem is if exact match is there for the columns <code>col1</code>, <code>col2</code> and <code>col3</code> in the table, it should consider that only not the rows with '<code>%</code>' value. For example, for the <code>Id=1</code> in the <code>table1</code>, the result should be from <code>id=4</code> in the <code>table2</code>.<br>
  I tried with following query:</p>
  
  <pre><code>UPDATE table1 
  SET name = t2.Name
  FROM (SELECT TOP 1 
          t1.id, t2.name 
        FROM table1 t1 
          INNER JOIN table2 t2 
          ON t1.col3 = t2.col3 AND t1.col1 LIKE t2.col1 
                               AND t1.col2 LIKE t2.col2 
        ORDER BY t2.col1, t2.col2) AS t3
  WHERE id = t3.id;
  </code></pre>
  
  <p>But I am not getting result which I expected. And also, there are 8,000,000 records are there in <code>table1</code> so it should not affect performance.<br>
  Please help to fix this issue.</p>