How to limit update to single row

Question

Command

update invoice set unpaid = unpaid - 123
    where regexp_replace(invocienumber, '-.*', '', 'g') ='12345'
returning *

updates all invoices whose number start with 12345.

How to fix this so than only one invoice is updated and get back invoice which is updated ?

I tried

update invoice set unpaid = unpaid - 123
    where regexp_replace(invocienumber, '-.*', '', 'g') ='12345'
limit 1
returning *

but got error.

using PostgreSQL 13.2, compiled by Visual C++ build 1900, 64-bit

with psqlODBC

Looks pretty strange to me – you seem to select an arbitrary value, how do you know you selected the right one? — Aconcagua, Aug 05 '21 at 14:33
Payment can clear any invoice. So every row is right one. However single payment must clear only one invoice. So no more than 1 row should updated. — Andrus, Aug 05 '21 at 19:39
Ah, so invoice number contains a client identifier? This would mean that the db is not normalised, though, which is why you got into that trouble at all. Selecting an arbitrary invoice might then still be problematic if there are due dates involved (the one being due first should get paid first). Side note: Have you considered payment amount being larger than remaining debts? — Aconcagua, Aug 06 '21 at 12:33
There is no client id in invoice number. If goods are in different stores, single invoice is splitted into multiple rows all with same number. Invoices are created only after payment is received in e-shop. No need for due date. If payment is larger, unpaid amount will become negative and then payment is manually split into multiple invoices and binding to one invoice. — Andrus, Aug 07 '21 at 07:55

eshirvana · Accepted Answer · 2021-08-05T19:43:34.317

2

not beautiful but here is one way :

update invoice 
set unpaid = unpaid - 123
where id = (
     select id from invoice 
     where regexp_replace(invocienumber, '-.*', '', 'g') ='12345'
     limit 1
)
returning * ;

alternatively you can use a cte instead of subquery and join with cte to update your table

edited Aug 05 '21 at 19:43

answered Aug 05 '21 at 14:22

eshirvana

23,227
3
22
38

Same invoicenumer can be in multiple rows. In this case this query updates multiple rows. How to fix it so that no more than one row is updated ? – Andrus Aug 05 '21 at 19:41
@Andrus then do you have a unique key ? replace invocienumber with your unique key ( like your primary key). like id , see updated answer – eshirvana Aug 05 '21 at 19:43
How to implement this if there is no unique id ? – Andrus Aug 05 '21 at 20:01
@Andrus then better to add one , 1NF is the first thing you need to have normalized tables. add an auto increment integer column and make it PK. – eshirvana Aug 05 '21 at 20:06
Perhaps a better idea, Do not keep payments as just a reduction the unpaid amount. Instead create a payments table containing the same invoice number with date payment made and amount of payment. You can then derive unpaid amount when requires and provide invoice / payment history. – Belayer Aug 05 '21 at 20:23
Payment table already exists. There large number of invoices and unpaid is usually only small amount of them. Not sure is summing 10 years of payment data and comparing it with 10 years of invoices for every unpaid invoice retrieval is good idea. So unpaid amount is duplicated also in invoice table. This allows to use index for unpaid column to find unpaid invoices fast. – Andrus Aug 05 '21 at 20:46

How to limit update to single row

1 Answers1