[sqlite] any sql command to synchronize two table
op 10-11-2015 10:53 schreef YAN HONG YE op yanhong.ye at mpsa.com: > Hi, > I have two table with same field: > > Id int, > Content text, > Inserttime datatime, > Updatetime datatime > > I want to do this: > If id not in the other table, then insert it, > if id is same, and inserttime diffirent, then insert it to the other table ; > if id is same, and updatetime diffirent, then update it to the other table ; > I don't know if exist this tool, the target is synchronize two table to the > same except ID field. As Simon Slavin said, this is not a single command. INSERT OR REPLACE does not cover deletes. If the Id column were defined as INTEGER PRIMARY KEY, then the INSERT OR REPLACE statement can help. If I understand it right, it should not matter if you update the other table or just replace a complete row. Some filter (WHERE clause) is still needed to not rewrite the complete table. This could look like: INSERT OR REPLACE INTO t2 SELECT * FROM t1 LEFT OUTER JOIN t2 USING (id) WHERE t2.id IS NULL -- id not in the other table OR t2.inserttime <> t1.inserttime --if id is same, and inserttime diffirent OR t2/updatetime <> t1.updatetime --if id is same, and updatetime diffirent ; Or even shorter: INSERT OR REPLACE INTO t2 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ;
[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"
op 13-09-2015 01:45 schreef E.Pasma op pasma10 at concepts.nl: >> >> On 2015-09-13 12:40 AM, Aurel Wisse wrote: >>> @R.Smith >>> I wasn't aware that recursive aggregate queries give wrong answers >>> in 99% >>> of all general use cases//... >> >> I apologize - I thought Richard had made that much clear early on in >> the exchange and you were simply not recognizing or accepting the >> fact, which led to my unnecessarily verbose reply. (Also, 99% might >> be an exaggerated figure, but the principle remains). >> >> Either way, I am happy you have now a query that is even faster to >> completion than the original one in dispute. Be sure to post any >> query that takes too long, someone here might have a way of speeding >> it up. >> >> Cheers, >> Ryan >> > Apart from speed also the results are changed. Using drh's securities > table with 1.000 rows, I get the following line counts: > > 999 - aurel's original version is 3.8.9 > 1000 - Luuks non-recursive version, same for my own trial > 827 -the version with temp table > 838 - DRH;s version > > And the winner is.. > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Excuse, it is getting late here: the temp table version returns just one row less then DRH's version.
[sqlite] Third test of json and index expressions, now it works
op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com: > On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma wrote: > >> >> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven: >> >>> >>> One, the argument wasn't whether or not column expressions were called >>> multiple times. My understanding of the debate was that using aliases in >>> place of their definitions made code more understandable and more easily >>> maintained than gratuitous repetition of the definitions. >>> >>> Two, you used a different query. Looking at my query again (fleshed out >>> since I'm on a computer vs my phone): >>> >>> sqlite> create table c(a); >>> sqlite> insert into c values(1); >>> sqlite> insert into c values(2); >>> sqlite> insert into c values(3); >>> sqlite> insert into c values(4); >>> sqlite> select * from (select a as b from c) where b > 2; >>> 3 >>> 4 >>> sqlite> explain query plan select * from (select a as b from c) where b > >>> 2; >>> 0|0|0|SCAN TABLE c >>> >>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2; >>> 3 >>> 4 >>> sqlite> explain query plan select * from (select abs(a-a-a) as b from c) >>> where b > 2; >>> 0|0|0|SCAN TABLE c >>> >>> Clearly, this gives you an opportunity to replace definitions with >>> aliases, >>> since the aliases in the inner query become the column names of the outer >>> query. >>> >>> Your example doesn't do the same thing at all (ignoring the difference >>> between your condition of > 0 and mine of > 2). Your query is made of >>> scalar subqueries and you will never get more than a single row back. >>> >>> The following two queries are not the same: >>> >>> select 1 from (select (select a from c) as b) where b > 0; >>> >>> select 1 from (select a as b from c) where b > 0; >>> >>> In any case, this is a perfectly good option to query building that don't >>> require non-standard behavior (as far as I can tell) while still giving >>> the >>> benefit of DRY (don't repeat yourself) as someone noted previously. It's >>> more verbose. It may not be as intuitive. But it works. >>> >>> I am not a SQL guru. If I am wrong about my alternative invoking only >>> standard behavior, my apologies. >>> >>> >> Yes. the debate is about column aliases. >> Still the OP also wished to not call column expressions multiple times. >> And I had to change your query to make that appear in the query plan. >> But here is an example very close to yours where b is an alias for >> random(). >> I changed thea original where clause (b>2) to b <> b abd I hope it is >> obvious >> that the function is caled multiple times for each row. >> >> create table c(a); >> insert into c values(1); >> insert into c values(2); >> insert into c values(3); >> insert into c values(4); >> select * from (select random() as b from c) where b <> b; >> 232218896271007264 >> -1043354911054439855 >> 5116834959932449572 >> 7115658816317887453 >> > > Interesting query. So it would seem then that a temp table would be > required for something like this to avoid calling random three times per > row. I would have expected *this* version to return no rows. I guess this > is why we test software. :) > A temp table is not required. Another feature of subqueries (apart from sticking to standard use of aliases) is that you can make them behave as temp tables. Just by adding some "hint" for the optimizer such as LIMIT 99 or -1. select * from (select random() as b from c limit -1) where b <> b; Much easier than a temp table but not guaranteed to ever remain to work.
Re: [sqlite] Different result from experimental query
The query should indeed return one row. Sorry I forget to say that. The NOT NULL for id in table t does not make any difference. It was only added to rule out that it might make any difference.. op 28-08-2013 16:29 schreef Marc L. Allen op mlal...@outsitenetworks.com: > Looks like that should return one row, yes? I wonder if operator precedence > is broken for that query and the OR is binding higher than the AND. Also > possible is that the NOT NULL for id in table t is messing up some query > optimization with t2.id NOT NULL. > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of E.Pasma > Sent: Wednesday, August 28, 2013 10:20 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Different result from experimental query > > An experimantal query, involving OUTER JOIN with BETWEEN and JOIN with a > combined OR and AND expression, does not return all expected rows. I tried > this just after SQLite 3.8.0. was released and found that the issue is > particular to this version. At least it is alright in version 3.7.17. > Below is a simplified case. This is still complex but if you leave out > anything further, the problem no longer occurs. Hope it is useful to report > this. > > .echo on > .version > SQLite 3.8.0 2013-08-26 04:50:08 > f64cd21e2e23ed7cff48f7dafa5e76adde9321c2 > CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL) ; INSERT INTO t VALUES (1) ; > SELECT * > FROMt t1 > LEFT OUTER JOIN t t2 ON t2.id BETWEEN 10 AND 20 > JOINt t3 ON > ( > t3.id = t1.id > OR t2.id IS NOT NULL AND t3.id = t2.id > ) > ; > > -- E Pasma > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > This email and any attachments are only for use by the intended recipient(s) > and may contain legally privileged, confidential, proprietary or otherwise > private information. Any unauthorized use, reproduction, dissemination, > distribution or other disclosure of the contents of this e-mail or its > attachments is strictly prohibited. If you have received this email in error, > please notify the sender immediately and delete the original. > ___ > 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
