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 > > -- View this message in context: http://old.nabble.com/Help-with-complex-UPDATE-question-tp29239594p29249685.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
