Problem inserting database row using last row in sqlite

Question

I'd like to be able to do the following initially and also at anytime.

insert into balance (closing_amount, opening_amount, created, tx_id)

select closing_amount + :value, closing_amount, :date, :tx_id from balance order by id desc limit 1

Basically I'm inserting by using previous values. But if there are no values to begin with, nothing gets inserted.

I could use a union to which works the first time but duplicates on subsequent inserts.

I want to avoid two trips. Is there a way to do this?

Also, the tx_id will always be unique.

Answer 1

I think you want something like this:

insert into balance (closing_amount, opening_amount, created, tx_id)
select coalesce(max(closing_amount), 0) + :value, 
       coalesce(max(closing_amount), 0), 
       :date, 
       :tx_id 
from (       
  select closing_amount 
  from balance 
  order by tx_id desc
  limit 1
) t;

You only need the last closing_amount, so max(closing_amount) from the subquery, which returns 1 row or none at all, will return that closing_amount or null respectively.

See a simplified demo.