title: SQL: Engineering new features using UPDATE SET
body: <p>I'm trying to create a new feature, user_count, which displays the number of times a particular user appears in a dataset. Here is a snapshot of the original dataset:</p>
  <p><a href="https://i.stack.imgur.com/j4coz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j4coz.png" alt="dataset" /></a></p>
  <p>Here is the SQL code that I have written to try to achieve this (used in GoogleCloud's BigQuery):</p>
  <pre><code>UPDATE `project-feat-eng.ednetv1.smalltrain` AS t
  SET t.user_count = target.total_questions_answered
  FROM (
  SELECT user_id, count(user_id) AS total_questions_answered
  FROM `project-feat-eng.ednetv1.smalltrain` 
  GROUP BY user_id) AS target
  WHERE target.user_id = t.user_id
  </code></pre>
  <p>The problem is that user_count is not a feature in the original table, so cannot be updated. Can the above code be adapted to create the user_count feature successfully?</p>