I can think of several answers to your last question; #1 Your mileage may vary #2 Take the easiest way for you and see it works well. #3 Since SQL wasn't designed for vector math do it in your program -- probably MUCH clearer to anybody (including yourself) in the future. It shouldn't take more than about 30 minutes to code this up. I was tempted to do it myself just for the exercise... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of peterwinson1 Sent: Fri 7/23/2010 9:37 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Help with complex UPDATE question Jim, I see what you mean. Would it be faster then if I read the from the table, do the math in a program, and then insert the values back into the table? Or would it faster to amend the table as you suggest and use SQL UPDATE? I wonder. Jim Morris-4 wrote: > > AYou 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 > > -- View this message in context: http://old.nabble.com/Help-with-complex-UPDATE-question-tp29239594p29252817.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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
