title: TSQL Update statement with Join
body: <p>I have two tables with a parent/child relationship. I want to update the parent with data from the child. However assuming there are 2 children I would like to be able to pick which child is used for the update depending on some other column from the child.
  Here is what I have so far:
  Parent: @test
  Child: @exdat
  Expected outcome, result of parent after update should contain only Capital letters. I want to update the parent with some data from the child, but if more than one exists I'd rather choose a child with a dimp of p1 over p2, p2 over p3 and p3 over p4</p>
  
  <pre><code>DECLARE @test TABLE
  (
    id  int,
    val char(1)
  );
  DECLARE @exdat TABLE
  (
    id  int,
    dval char(1),
    dimp char(2)
  );
  INSERT INTO @test (id,val)
              SELECT 1,'a'
      UNION ALL   SELECT 2,'b'
      UNION ALL   SELECT 3,'c'
      UNION ALL   SELECT 4,'d'
      UNION ALL   SELECT 5,'e'
      UNION ALL   SELECT 6,'f'
      UNION ALL   SELECT 7,'g'
  ;
  INSERT INTO @exdat (id,dval,dimp)
              SELECT 1,'A','p1'
      UNION ALL   SELECT 2,'B','p3'
      UNION ALL   SELECT 3,'C','p1'
      UNION ALL   SELECT 4,'D','p2'
      UNION ALL   SELECT 5,'E','p2'
      UNION ALL   SELECT 6,'F','p3'
      UNION ALL   SELECT 7,'w','p2'
      UNION ALL   SELECT 7,'g','p3'
      UNION ALL   SELECT 7,'G','p1'
      UNION ALL   SELECT 7,'z','p4'
  ;
  UPDATE @test SET
      val = e.dval
  FROM
      @test t
      INNER JOIN @exdat e ON t.id = e.id 
  ;
  SELECT * FROM @test;
  
  Result:
  1   A
  2   B
  3   C
  4   D
  5   E
  6   F
  7   w &lt;-- problem illustrated here 
  </code></pre>
  
  <p>This "w" could have been any of the values w,g,G,z. So I am asking how can I prioritize the child selection based on some other column ? </p>