You must add additional data to the rows so you can refer to them unambiguously.
table1 (KEY, COL1, ord) 0, 1,1 0, 2,2 1, 3,1 1, 4,2 2, 5,1 2, 6,2 3, 7,1 3, 8,2 On 7/23/2010 12:16 PM, peterwinson1 wrote: > Jim you maybe correct that I don't have enough data to unambiguously identify > the rows. But just in case I was not very clear the first time. > > What I want to do is take the COL1 values of the first 2 rows [1, 2] and > subtract them from the COL1 values, two rows at a time. so [1, 2] - [1, 2], > then [3, 4] - [1, 2], then [5, 6] - [1, 2], and finally [7, 8] - [1, 2]. > The question is can I do this just using SQL? > > > > > Jim Morris-4 wrote: > >> What you are trying to do is unclear to me. It seems that table1 >> doesn't have enough data to unambiguously identify the rows. >> >> On 7/23/2010 8:03 AM, peterwinson1 wrote: >> >>> Thanks Eric and Alan for your help. I tried to apply your code to my >>> problem >>> and it works to a limited extent because the problem is more complicated >>> than the example I gave in the post. I tries to simplify my exact >>> problem >>> but that didn't work out. So here is the problem that I trying to solve. >>> >>> table1 (KEY, COL1) >>> >>> 0, 1 >>> 0, 2 >>> 1, 3 >>> 1, 4 >>> 2, 5 >>> 2, 6 >>> 3, 7 >>> 3, 8 >>> >>> table2 (KEY, X, Y) >>> >>> 0, 0, 0 >>> 1, 0, 1 >>> 2, 1, 0 >>> 3, 1, 1 >>> >>> What I would like to do is, like before, subtract COL1 from COL1 where >>> table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But >>> I >>> want to do a vector subtraction instead of a scalar subtraction. >>> >>> So far I have >>> >>> UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE >>> table1.KEY IN (SELECT table2 WHERE X=0) >>> >>> The result I would like to get is >>> >>> table1 >>> >>> 0, 0 //(1 - 1) >>> 0, 0 //(2 - 2) >>> 1, 2 //(3 - 1) >>> 1, 2 //(4 - 2) >>> 2, 5 >>> 2, 6 >>> 3, 7 >>> 3, 8 >>> >>> Instead I get >>> >>> 0, 0 //(1 - 1) >>> 0, 1 //(2 - 1) >>> 1, 2 //(3 - 1) >>> 1, 3 //(4 - 1) >>> 2, 5 >>> 2, 6 >>> 3, 7 >>> 3, 8 >>> >>> Is this possible in SQL? >>> >>> >>> peterwinson1 wrote: >>> >>> >>>> Hello, >>>> >>>> I have a some what complex question about UPDATE. I have the following >>>> table >>>> >>>> table1 (KEY, COL1) >>>> >>>> 0, 1 >>>> 1, 2 >>>> 2, 3 >>>> 3, 4 >>>> >>>> What I would like to do is to UPDATE COL1 by subtracting the COL1 value >>>> where KEY = 0 from the COL1 value of the current row so that the result >>>> would be. >>>> >>>> 0, 0 >>>> 1, 1 >>>> 2, 2 >>>> 3, 3 >>>> >>>> Can this be done in SQL? It does not have to be one UPDATE/SELECT >>>> statement. >>>> >>>> Thank you >>>> pw >>>> >>>> >>>> >>>> >>>> >>> >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
