sqlpostgresqlsql-insertinsert-select

INSERT INTO from multiple sources/tables


I have a table FinalTable with these columns:

name, lastName, pesel, position, id_operator

I want to fill my FinalTable with values from 2 other tables:

I want to join AAA and BBB on the pesel column

insert into FinalTable (name, lastName, pesel, position, id_operator)
    select 
        name, lastName, pesel, position, 
        (select id_operator from BBB b where b.pesel = a.pesel) 
    from 
        AAA a;

How to do that? I'd like to set my last column id_operator to the value from BBB. The SQL query above is incorrect.


Solution

  • I'd insert a join query:

    INSERT INTO FinalTable  (name, lastName, pesel, position, id_operator)
    SELECT a.name, a.lastName, a.pesel, a.position, b.id_operator
    FROM   AAA a
    JOIN   BBB b ON pesel = a.pesel;