CODEWITHSUNDEEP
androidsqlsqlite
Labeeb Panampullan
Labeeb Panampullan

Reputation: 34823

Update one column as sum of other two columns

I need to update each row of a table with one column as sum of other two columns in the same table

Something like this

UPDATE table1 SET table1.column1 = sum (table1.column1 + table1.column2) for every row

I have tried

This is working for me

UPDATE table1 SET column1 =(SELECT  SUM(column1 + column2)  FROM table1 where rowid = 1) WHERE rowid = 1

So I can do this by iterating each rowid by first selecting all rowId 

for( all rowid as i){
    UPDATE table1 SET column1 =(SELECT  SUM(column1 + column2)  FROM table1 where rowid = i) WHERE rowid = i
    }

But I need to do for all the rows in that table in one query

When I tried:

update table1  set column1  = (select (column1  + column2) from table1 )

this will summ all the values of column1 and column2 i want to do wrt to a row

Any idea?

Me working in sqLite for Android

Upvotes: 12

Views: 23083

Answers (4)

user12144543
user12144543

Reputation: 1

I found a cte worked best for me

;with new_count
as
(select rownumber
    ,sum(isnull([col1],0) + 
    isnull([col2],0) + 
    isnull([col3],0) + 
    isnull([col4],0) + 
    isnull([col5],0)) as [New Count]
    from #tbl1
    group by rownumber
)
update t1
    set t1.[New Count] = isnull(nc.[New Count],0)
    from new_count nc
    join #tbl1 t1
    on t1.rownumber = nc.rownumber

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21766

For all the rows you need not WHERE predicate:

UPDATE table SET
  column1 = column1+column2

thats all.

Upvotes: 3

Andomar
Andomar

Reputation: 238086

It's allowed to read from columns in the set clause:

UPDATE  table1 
SET     column1 = column1 + column2
WHERE   rowid = 1

Upvotes: 3

Mark Byers
Mark Byers

Reputation: 838186

There's no need for loops or inner selects. Just try this:

UPDATE table1 SET column1 = column1 + column2

Upvotes: 26

Related Questions