[SOLVED] postgresql join and insert

postgresql join and insert

I have two tables, account_subscriptions and order_item and I wrote this query:

Select a1.accounts_id,sum(a2.quantity * a2.unit_price) as MRR
from account_subscriptions a1 
inner join order_item a2 on a1.subscription_id = a2.account_subscriptions_id 
group by a1.accounts_id;

With this code I am able find the total_bill as MRR along with its corresponding account_id. But now I want to put it in an existing table named summary, where there is already columns along with account_id and MRR column which is empty.

I want to put the values of MRR I got from my query into the MRR column with matching the corresponding account_id of summary table and account_subscriptions table.

Solution

try updating, not inserting:

with c as (
Select a1.accounts_id,sum(a2.quantity * a2.unit_price) as MRR
from account_subscriptions a1 
inner join order_item a2 on a1.subscription_id = a2.account_subscriptions_id 
group by a1.accounts_id
)
update summary s set MRR = c.MRR 
from c
where c.accounts_id = s.accounts_id

update reconciling your comments, to wrap it up to function use

create function fn_name_here() returns int as
$$
begin
    with c as (
    Select a1.accounts_id,sum(a2.quantity * a2.unit_price) as MRR
    from account_subscriptions a1 
    inner join order_item a2 on a1.subscription_id = a2.account_subscriptions_id 
    group by a1.accounts_id
    )
    update summary s set MRR = c.MRR 
    from c
    where c.accounts_id = s.accounts_id;
return 0;
end;
$$ language plpgsql
;

Please start reading and practicing SQL and/or plpgsql - just wrapping the code in function does not make much sense. With no logic inside...