Update a Column from another Column using SQLite?

Question

I have a DB with two columns with many records and from these two columns I have to do some mathematical operations and create other two columns.

For now I did like this:

• I made a SELECT and read the two columns, and put everything in a List
• Then I go through the List and UPDATE the table line by line

Do you think there is a faster way to do it? I also tried like this:

UPDATE myTable SET X_GAUSS = (SELECT X FROM myTable ) + 1, Y_GAUSS = (SELECT Y FROM myTable) + 2

(it's only an example)

But in this way every line of the new columns is the same as the line before, instead I want something like:

X  Y  X_GAUSS Y_GAUSS
1  2  2       4
3  4  4       6
5  6  6       8
...  

Answer 1

A subquery like SELECT X FROM myTable returns the first row of the table.

You can simply access the columns of the same row directly:

UPDATE myTable
SET X_GAUSS = X + 1,
    Y_GAUSS = Y + 2;