'Postgres - Select and Update multiple rows based on condition
I need the following thing:
I have 2 tables (users and invoices) and the invoices have a foreign key userId, totalToPay, paidAmount, isPaid.
One user can have multiple invoices.
invoice_1 : id:1, isPaid: false, totalToPay: 80, paidAmount: 0, userId: 1,
invoice_2 : id:2, isPaid: false, totalToPay: 40, paidAmount: 0, userId: 1,
Let's say user input is $100. Now, I need to select all invoices from userId = 1 and which are not paid, and then I need to update that invoices (paidAmount and isPaid columns) trough loop, but so that invoice_1 is paid $80 and isPaid = true, and invoice_2 only $20, because 100 - 80 = 20. So after that it needs to look like this:
invoice_1 : id:1, isPaid: true, totalToPay: 80, paidAmount: 80, userId: 1,
invoice_2 : id:2, isPaid: false, totalToPay: 40, paidAmount:20, userId: 1,
I know how to select, but what next?
SELECT invoice.id, totalToPay, paidAmount, isPaid, users.name, createdAt
FROM invoices
INNER JOIN users ON invoices.userId=users.id
WHERE invoices.userId = 1 and invoices.isPaid = false
ORDER BY invoices.createdAt
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
