title: Postgresql Update Based on count, min and group by
body: <p>Thank you for taking the time to look at my question.
  I've seen similar questions, but not the same depth. Please help!</p>
  
  <p>I would like to update a column all rows in a table that holds user_id and date_created with the lowest date_created for the user_id.</p>
  
  <p>The following select gives me all the rows I would like to update:</p>
  
  <pre><code>select user_id, min(date_created) from mytable s1 where 
      (select count(1) from mytable s2 where 
          s1.user_id = s2.user_id group by s2.user_id) 
   &gt; 1 group by user_id order by user_id;
  </code></pre>
  
  <p>I would have expected this update to work:</p>
  
  <pre><code>update mytable set join_status = 1 where date_created = 
      (select min(date_created) from mytable s1 where 
          (select count(1) from simplepay_payment s2 where 
              s1.user_id = s2.user_id group by s2.user_id)
    &gt; 1 group by user_id);
  </code></pre>
  
  <p>But is gave the following error:</p>
  
  <pre><code>ERROR:  more than one row returned by a subquery used as an expression
  </code></pre>
  
  <p>I've tried a few different solutions, but nothing seems to help.</p>
  
  <p>Does anyone have any ideas fro me?</p>
  
  <p>Thanks again.</p>