title: Using max-function in subquery relating to main query
body: <p>I have the following problem:
  I have two tables <code>TAB_1</code> and <code>TAB_2</code>.</p>
  
  <p>In <code>TAB_1</code> I would like to fill in column <code>FEAT_A1</code> with a matching value in <code>TAB_2</code>. Hence, I join both tables on their common feature <code>FEAT_B</code>.</p>
  
  <p>Unfortunately, per <code>FEAT_B2</code>-ID in <code>TAB_2</code> there are multiple records. I would like to use the matching record, for which <code>FEAT_D2</code> is maximal.</p>
  
  <p>I can perform this operation with the code shown below. Unfortunately, the code is super slow since, per match, I have to perform one order- and one fetch-operation.</p>
  
  <p>Can you think of a more efficient way to program this?</p>
  
  <p>To clarify things, here is an example of <code>TAB_2</code> records:</p>
  
  <pre><code>FEAT_A2     FEAT_B2    FEAT_C2         FEAT_D2
  "A"           42      "SOMETHING"        7
  "B"           42      "SOMETHING"        11
  "D"           42      "SOMETHING"        1
  "A"           42      "SOMETHING"        3
  </code></pre>
  
  <p>In <code>TAB_1</code>, for <code>FEAT_B1 = 42</code>, I would like to set <code>FEAT_A1 = "B"</code>.</p>
  
  <pre><code>UPDATE TAB_1
  SET TAB_1.FEAT_A1 = (
      SELECT FEAT_A2 FROM TAB_2
      WHERE TAB1.FEAT_B1 = TAB2.FEAT_B2
          AND TAB2.FEAT_C2 = 'SOMETHING'
      ORDER BY TAB2.FEAT_D2 DESC
      FETCH FIRST 1 ROW ONLY);
  </code></pre>