SQLite select inside of update -

so there 2 tables, transactions created_at column , transaction_associations amount , remaining_balance columns, among others. need calculate running sum(total) on amount column, sorted created_at column, obviously. problem need sum of transactions created before current transaction being calculated. would've needed select inside update query in order select current_transactions table in order hold of current created_at date. can't. missing something? there alternatives method?

update transaction_associations set remaining_balance =  (      select sum (transaction_associations.amount)      transactions      join transaction_associations on transactions.id = transaction_id      created_at <= current_transactions.created_at // here )  id in (     select id      transaction_associations      join transactions on transactions.id = transaction_id      created_at >= '2014-11-24' ) 

edit: added example.

transactions     transaction_associations created_at       amount     remaining_balance 2014-02-01       100        100 2014-03-01        50        150 2014-04-01       205        355 

later edit: added complete code use on sqlfiddle. i've replaced transaction_associations ta2 on sum, complains of misuse of aggregate: sum()

drop table if exists transactions; drop table if exists transaction_associations; create table transactions ( id integer,  created_at text); create table transaction_associations ( id integer,  amount integer, remaining_balance integer, transaction_id integer);    insert transactions values (1,'2015');   insert transactions values (2,'2014');   insert transactions values (3,'2013');   insert transactions values (4,'2012');   insert transactions values (5,'2010');   insert transaction_associations values (6, 100, 0, 1);   insert transaction_associations values (7, 20, 0, 2);   insert transaction_associations values (8, 3, 0, 3);   insert transaction_associations values (9, 40, 0, 4);   insert transaction_associations values (10, 500, 0, 5);    update transaction_associations set remaining_balance = (     select sum(ta2.amount)      transactions      join transaction_associations ta2 on transactions.id = ta2.transaction_id     created_at <= (select created_at                          transactions                          id = ta2.transaction_id) )  transaction_id in (     select id     transactions     created_at >= '2013' );  select * transactions join transaction_associations on transactions.id = transaction_associations.transaction_id; 

this results in, wrong:

1   2015    6   100 663 1 2   2014    7   20  663 2 3   2013    8   3   663 3 4   2012    9   40  0   4 5   2010    10  500 0   5 

result should be:

1   2015    6   100 663 1 2   2014    7   20  563 2 3   2013    8   3   543 3 4   2012    9   40  0   4 5   2010    10  500 0   5 

to use same table name multiple times, rename 1 of them. not possible update, have in select. corresponding timestamp, use subquery. simplifications, becomes:

update transaction_associations set remaining_balance = (     select sum(ta2.amount)      transactions      join transaction_associations ta2 on transactions.id = ta2.transaction_id     created_at <= (select created_at                          transactions                          id = transaction_associations.transaction_id) )  transaction_id in (     select id     transactions     created_at >= '2014-11-24' );