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
