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
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