SQL sqlite conditional query in sqlite (for Android)

Question

Hello I have a table with that structure:

ID      VALUE
1         3
2         5
3        12

if I query select id,value,value+5 from table. Thats the result

ID      VALUE    NEW_VALUE
1         3         8
2         5         10
3        12         17

And what I want to make a query indicating the id and the new value that return the whole table but with a 3rd column indicating the new values after inserting. for example for myQuery(id=2,value=8)

ID      VALUE    NEW_VALUE
1         3         3
2         5         8
3        12         12

Is posible to do that in the same query?

I hope it is just me ... But I really didn't understand/ Can you explain — Sherif elKhatib, Aug 02 '11 at 11:18
I'm trying to get a table with notes of students, so if the student has the given id, enter the new value in his note and if not return the value unmodified — Addev, Aug 02 '11 at 11:26
ok so you want a query where you give it 1 id and 1 value so it returns the whole table with only the row with this id changed? — Sherif elKhatib, Aug 02 '11 at 11:39
Still not clear to me either. What do you mean by "and if not"? — Tim, Aug 02 '11 at 11:40

score 2 · Accepted Answer · edited May 23 '17 at 12:11

YOu can use the WHERE clause to select only the rows you want ("...if the student has the given id..."):

             update T
             set col3 = col2 + 5
             where id = 2

Of course, col3 would have to exist before you can update it. So you will either have to issue an ALTER-TABLE statement (if your implementation supports it) or recreate the table with the desired columns, import the original data (INSERT INTO YOURNEWTABLE...SELECT ... from YOUROLDTABLE) and then update col3.

If you don't want to "persist" this third column but only need it to be displayed when you query:

            select id, col2, col2 + 5 as myComputedValue 
            from T
            where id = 2

Finally, if you want to display all rows but change the addend conditionally (add zero to col2 when the id is not one of the ones you desire but add 5 when it is) then you can use the CASE statement.

SQL sqlite conditional query in sqlite (for Android)

1 Answers1