sqlpostgresqlsql-updatelimitpsqlodbc

How to limit update to single row

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

Solution

  • 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