title: How do I update a table with a select statement that produces multiple rows?
body: <p>I have two tables:</p>
  
  <pre><code>[Table container]
  id int
  &lt;some more fields&gt;
  latest_measurement int
  
  [Table measurement]
  id int
  container_id int
  unixtime bigint
  </code></pre>
  
  <p>Now I want to update the sensors table <code>latest_measurement</code> column based on the latest measurement from the measurement table. I have prepared the subquery to return the latest measurement per <code>sensor_id</code> and this works:</p>
  
  <pre><code>SELECT m.fill_level
  from measurement m
  inner join (
      select m.container_id, MAX(m.unixtime) as maxdate from measurement m GROUP BY container_id
  ) m2
  on m2.container_id = m.container_id
  where m.unixtime = m2.maxdate
  </code></pre>
  
  <p>But if I then use this query in my update statement as follows, it fails with an exception:</p>
  
  <pre><code>UPDATE container
  SET latest_fill_level = (
      SELECT m.fill_level
      from measurement m
      inner join (
          select m.container_id, MAX(m.unixtime) as maxdate from measurement m GROUP BY container_id
  ) m2
      on m2.container_id = m.container_id
      where m.unixtime = m2.maxdate
      and container.id = m.container_id)
  </code></pre>
  
  <p>And finally, here's the exception:</p>
  
  <pre><code>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.
  </code></pre>
  
  <p>How do I write an update statement that can update multiple rows with their respective values from a SELECT statement that produces multiple values?</p>