title: PostgreSQL - Update table with subquery from updated table itself
body: <p>I have a table with these values: "user_id, text, text_lang, user_lang".</p>
  
  <p>The field text_lang contains a locale shortcut ('en','de') representing the language of the text. I now want to set the (unknown/empty) 'user_lang' by selecting the text_lang that has the maximum occurrence for each user_id.</p>
  
  <p>I have a working select statement that returns the text_lang with the most occurrences for each user_id, but I cannot figure out how to update the user_lang of each row based on the ID.</p>
  
  <pre><code>SELECT user_id, text_lang
  FROM (
      SELECT  user_id,
          text_lang,
          max(text_lang_count) OVER (PARTITION BY user_id) max_count,
          text_lang_count
      FROM (
          SELECT  user_id,
                  text_lang,
                  COUNT(text_lang) AS text_lang_count
          FROM test
          GROUP BY user_id, text_lang
      ) AS xx
  ) AS xy
  WHERE tweet_lang_count = max_count
  </code></pre>
  
  <p>I assume (but am not certain) that <a href="https://stackoverflow.com/questions/839938/mysql-sql-update-with-correlated-subquery-from-the-updated-table-itself">MySQL/SQL: Update with correlated subquery from the updated table itself</a> contains something closely related to my question, but I have been unable to adapt that solution to this specific problem.</p>