jon jon - 2 months ago 15
SQL Question

Update rows having same id and name

Here is Table t1

id name amount close
1 jon 4000 0
1 jon 5000 0
1 jon 9000 0


i want sum of amount at the end of close column

id name amount close
1 jon 4000 0
1 jon 5000 0
1 jon 9000 18000


I'm try to update close

update t1 set close = (select sum(amount) from t1 where id = '1' group by id)


but this query update all row of close like this

id name amount close
1 jon 4000 18000
1 jon 5000 18000
1 jon 9000 18000

Answer

I think you want a correlated subquery:

update t1
    set close = (select sum(t11.amount)
                 from t1 t11
                 where t1.id = t11.id
                );

However, this will still update all the rows. You just want the last one. Well, in SQL, rows are unordered, but SQLite offers rowid. This allows you to do:

update t1
    set close = (select sum(t11.amount)
                 from t1 t11
                 where t1.id = t11.id
                )
    where rowid = (select max(rowid)
                   from t1 t11
                   where t1.id = t11.id
                  );