Problems with updating column from another table in postgresql

Question

My db is postgresql. I have 4 tables and need to copy id of "payment_option" record to column in "settlement" table.

Quick info about model: Account has N invoices, Invoice has N settlements. Settlement has one of four payment options that are currently determined by column with string (PAYPAL, STRIPE, LEGACY, WIREPAYMENT). Account has N payment_options (always 4 in current state. Each type once) and each of these payment options has again one of those types in a column as string (PAYPAL, STRIPE, LEGACY, WIREPAYMENT). I need to add fkey pointing from settlement table to payment_option table that I can find settlement->invoice->account->payment_option

For this I need to do multiple join and do something like "UPDATE JOIN" but I am failing for some reason. My statement updates all settlement.payment_option_id on id of the first payment_option but not on id of payment_option bound through the account and invoice tables.

update settlement 
set payment_option_id = P.id
from payment_option as P 
inner join invoice as I on P.account_id = I.account_id
inner join settlement as S on S.invoice_id = I.id
where S.payment_option_type = P.type;

How to write it correctly and what is wrong? I think issue is in "SET" becuse when I write select with following joins it works as expected

Thanks Lukas

Gordon Linoff · Accepted Answer · 2020-01-03 19:31:52Z

2

It seems strange that you are repeating the the reference to settlement in the FROM clause. Perhaps you intend:

update settlement s
    set payment_option_id = P.id
    from payment_option P join
         invoice I
         on P.account_id = I.account_id
    where S.payment_option_type = P.type and S.invoice_id = I.id

answered Jan 3, 2020 at 19:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukas S Over a year ago

Thank you.This solved my issue. I haven't been writing such statement yet

Collectives™ on Stack Overflow

Problems with updating column from another table in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related