sql - Updating a table with the result of a query on 2 other tables -

i need update column on table value result of query. however, customerid column on receiving table needs matched customer id column on query.

here query far:

insert paymentfacilities (policyid)  (select        c.id, p.policyid            customers c inner join                      policies p on c.id = p.customerid        (not (p.policyreference null) , p.cancelled = 0)) 

in example above want insert p.policyid field select query policyid column of paymentfacilities table customerid field of paymentfacilities matches c.id field select query.

thanks help

the number of columns in select statement should same insert statement.

you try removing columns except p.policyid1:

insert paymentfacilities (policyid)  (select        p.policyid            customers c inner join                      policies p on c.id = p.customerid        (not (p.policyreference null) , p.cancelled = 0)) 

keep in mind insert multple rows, , every time run query have duplicates.

if describe whole problem i'm sure people can find better solution.