title: MySQL UPDATE with a JOIN to a subselect
body: <p>I have a table called <code>sagepaydirect__transactions</code> which I am updating with a migration to set its <code>response</code> field to the <code>response</code> field of the table called <code>sagepaydirect__responses</code>.</p>
  
  <p>The two tenuously link via a table named <code>sagepaydirect__requests</code> on the last 4 digits of the _ separated string in the field <code>vendor_tx_code</code>.</p>
  
  <p>The problem is that <code>sagepaydirect__requests</code> will have multiple rows when joined to <code>sagepaydirect__transactions</code>. What I want to do is do the JOIN such that the UPDATE takes the value from the row in <code>sagepaydirect__transactions</code> whose ID is numerically higher.</p>
  
  <p>This is my attempt at the query but the complaint is that <code>t.id</code> does not exist.</p>
  
  <pre><code>UPDATE sagepaydirect__transactions t
  INNER JOIN (
          SELECT * FROM sagepaydirect__requests rq
          WHERE SUBSTRING_INDEX(rq.vendor_tx_code, '_', -1) = t.id
          ORDER BY id DESC
          LIMIT 1
  ) rq_table
  ON t.id = SUBSTRING_INDEX(rq_table.vendor_tx_code, '_', -1)
  INNER JOIN sagepaydirect__responses rs
  ON rq.id = rs.request_id
  SET t.response = rs.status_detail
  </code></pre>