CODEWITHSUNDEEP
sqlpostgresqlsql-insert
user11480956
user11480956

Reputation:

Copy data from one table to another(joined)

I have a problem in sql, in particular I have to copy data from one table to another. Tables have a 1-N relationship and I should copy a field, for example "weight" from table1 to all weight fields from table2 that are linked to the product. I thought about doing as follows, but it didn't work for me:

insert INTO table2 (weight) 
select weight 
from table1 
where table1.product_source_id = table2.source_id;

Any of you have any idea how this can be done?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

You want an update, not insert.

A generic approach uses a correlated subquery:

update table2
    set weight = (select t1.weight from table1 t1 where t1.product_source_id = table2.source_id);

Specific databases might have other approaches.

In Postgres, this would more commonly use a FROM clause:

update table2
    set weight = t1.weight
from table1 t1
where t1.product_source_id = table2.source_id;

Upvotes: 3

Related Questions