Update one column as the sum of two other columns

Question

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

Something like that

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

I 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, iterate over 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 rows in this table in one query

When I tried:

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

this sums all the values ​​of column 1 and column2 i want to do wrt in row

Any idea?

Me works in sqlite for Android

Answer 1

No need for loops or internal allocations. Just try the following:

 UPDATE table1 SET column1 = column1 + column2 

Answer 2

Allowed to read from columns in the set clause:

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

Answer 3

For all lines, you do not need a WHERE predicate:

 UPDATE table SET column1 = column1+column2 

thats all.

Answer 4

I found CTE worked better 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