insert a column from one table to another with constraints

postgresql, sql

Solution

If you are trying to modify existing rows, rather than add rows, then you need an `UPDATE` query, not an `INSERT` query.

It's possible to use joined tables as the target of an `UPDATE`; in your example:

UPDATE legend
JOIN temp
SET legend.account_id = temp.account_id
WHERE(temp.id = legend.advent_id);

Problem

I have 2 tables: legend and temp. 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. 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. ``` insert into legend(account_id) select temp.account_id from legend, temp where legend.advent_id=temp.id; ``` It is inserting the account_id in the wrong place, not to the corresponding advent_id. I am using the following query to check: ``` select advent_id, account_id from legend where account_id is not null; ``` What exactly is the problem in my insert query?

Original source