CODEWITHSUNDEEP
sqlsqlite
jon
jon

Reputation: 37

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

Upvotes: 1

Views: 1266

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

You can try this as well:

UPDATE testing1 a
INNER JOIN (SELECT MAX(amount) maxi, 
            SUM(amount) tot FROM testing1) b ON b.maxi = a.amount
SET a.close = b.tot

Upvotes: 0

user2040021
user2040021

Reputation: 309

Try following.

Update [Temp_Table]
      set [Temp_Table].[close] = (Select sum(amount) from [Temp_Table]  where id=1 group by ID)
      where amount = (Select max(amount) from [Temp_Table] group by id)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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

Upvotes: 1

Related Questions