title: insert a column from one table to another with constraints
body: <p>I have 2 tables: <em>legend</em> and <em>temp</em>. temp has an ID and account ID, legend has advent_id, account_id and other columns. ID in temp and advent_id in legend are similar. account_id column is empty and I want to import the relevant account_id from temp to legend. I am using PostgreSQL.</p>
  
  <p>I am trying the following query, but it is not working as I am expecting. New rows are getting created and the account_id's are getting added in the new rows, not to the corresponding advent_id.</p>
  
  <pre><code>insert into legend(account_id)
  select temp.account_id
  from legend, temp
  where legend.advent_id=temp.id;
  </code></pre>
  
  <p>It is inserting the account_id in the wrong place, not to the corresponding advent_id.</p>
  
  <p>I am using the following query to check:</p>
  
  <pre><code>select advent_id, account_id from legend where account_id is not null;
  </code></pre>
  
  <p>What exactly is the problem in my insert query?</p>