title: How to UPDATE a table entry based on the most recent entry with the same id in another table?
body: <p>Apologies if I am asking the obvious, but as a beginner, the obvious is not always that obvious.</p>
  <p>In an employee database, I am maintaining a table of positions the employee has had during his employment with the company. For the sake of simplicity, I will keep the code as short as possible.</p>
  <p>I have a temporary table based on the position table.</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TEMP TABLE IF NOT EXISTS tmp AS SELECT * FROM saireco.position LIMIT 0;
  </code></pre>
  <p>The temporary table gets the latest information from the source system. I remove unwanted entries from the temp table. Next, I update the temporary table if the EmployeeStatusCode in the temporary table is different from the EmployeeStatusCode in the position table (the SET command here does not have any particular meaning).</p>
  <pre class="lang-sql prettyprint-override"><code>UPDATE tmp t
    SET &quot;PositionCreationDate&quot; = '2022-05-16'
  FROM saireco.&quot;position&quot; p
  WHERE (t.&quot;EmployeeID&quot; = p.&quot;EmployeeID&quot;
     AND t.&quot;EmploymentStatusCode&quot; != p.&quot;EmploymentStatusCode&quot;)
  ;
  </code></pre>
  <p>The above code is flawed because the position table can contain multiple entries for the same employee (the temporary table tmp will only contain a maximum of one entry per employee). I have come up with the following code which uses the latest sequence id &quot;PositionID&quot;.</p>
  <pre class="lang-sql prettyprint-override"><code>UPDATE tmp t
    SET &quot;PositionCreationDate&quot; = '2022-05-16'
  FROM saireco.&quot;position&quot; p
  WHERE (t.&quot;EmployeeID&quot; = p.&quot;EmployeeID&quot;
     AND t.&quot;EmploymentStatusCode&quot; != p.&quot;EmploymentStatusCode&quot;)
     AND p.&quot;PositionID&quot; IN (SELECT MAX(pp.&quot;PositionID&quot;) FROM saireco.&quot;position&quot; pp WHERE pp.&quot;EmployeeID&quot; = t.&quot;EmployeeID&quot;)
  ;
  </code></pre>
  <p>My questions are twofold. Is this the correct way to achieve what I would like to achieve, e.g. update the temporary table if the most recent entry for the &quot;EmployeeID&quot; has changed; note the &quot;PositionID&quot; is a sequence which automatically gets incremented when a new entry is added to the table)? Secondly, because I already ensure the &quot;EmployeeID&quot; is equal here <code>SELECT MAX(pp.&quot;PositionID&quot;) FROM saireco.&quot;position&quot; pp WHERE pp.&quot;EmployeeID&quot; = t.&quot;EmployeeID&quot;</code>, does it mean I can get rid of the first condition <code>t.&quot;EmployeeID&quot; = p.&quot;EmployeeID&quot;</code>, resulting in the following code?</p>
  <pre class="lang-sql prettyprint-override"><code>UPDATE tmp t
    SET &quot;PositionCreationDate&quot; = '2022-05-16'
  FROM saireco.&quot;position&quot; p
  WHERE (t.&quot;EmploymentStatusCode&quot; != p.&quot;EmploymentStatusCode&quot;)
     AND p.&quot;PositionID&quot; IN (SELECT MAX(pp.&quot;PositionID&quot;) FROM saireco.&quot;position&quot; pp WHERE pp.&quot;EmployeeID&quot; = t.&quot;EmployeeID&quot;)
  ;
  </code></pre>