jeudi 10 mars 2016

SQL update a column value depending the value of another column

I am using sqlite. I have a table like this

create table t (i integer, j integer)
insert into t values(100,210)
insert into t values(200,290)
insert into t values(300,100)
insert into t values(400,500)

I'd like to adjust the j column so that 
* j=j+10 if i=j+10 exist
* j=j-10 if i=j-10 exist

After the adjust the table would look like
select i,j from t
i   j
--- ---
100 200
200 300
300 100
400 500

Is there an UPDATE syntax that would allow this, or should I do a loop with an hosting language (C in my case)

I tried to read the UPDATE statement BNF, I guess CASE, should help, but so far I can't make it, my problem is about 'exist' how do I check inside the UPDATE, that a computed 'i' exist.

Thanx in advance, cheers. Phi

Aucun commentaire:

Enregistrer un commentaire