How about this? BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT cl.* FROM client.LSOpenProjects cl JOIN LSOpenProjects ls ON (cl.ProjID = ls.ProjID AND cl.ProjID <= 133560 AND cl.XtraB >= '2014-08-07 00:00:00';); END;
I used join query to get existing records in local DB instead of subquery. 2014-08-10 7:30 GMT+09:00 jose isaias cabrera <jic...@cinops.xerox.com>: > > Greetings! > > Long story... But, I have to update a DB from another DB if the record in > the DB to be updated contains that record. So, with an attached DB, > > ATTACH 'somedb.sqlite' as client; > > I execute this command, > > BEGIN; > INSERT OR REPLACE INTO LSOpenProjects > SELECT * FROM client.LSOpenProjects > WHERE id IN > ( > SELECT id from client.LSOpenProjects > WHERE > client.LSOpenProjects.ProjID <= 133560 AND > client.LSOpenProjects.XtraB >= '2014-08-07 00:00:00'; > ); > END; > > This is great and it's working for one piece of the updates to a SharedDB, > but I also want to run another command like the one above for the usersDB > that will do the same, except that it should only update the id's that > exists in the localDB. In other words, only update from > client.LSOpenProjects, if the LSOpenProject table has that id. Imagine > these two DBs: > localUserDB: LSOpenProjects > id,...,XtraB > 20,...,'2014-04-09 10:20:34' > 23,...,'2014-04-09 10:20:34' > 27,...,'2014-04-09 10:20:34' > 28,...,'2014-04-09 10:20:34' > > SharedDB: LSOpenProjects > id,...,XtraB > ... > 20,...,'2014-08-09 14:23:14' > 21,...,'2014-08-08 11:29:25' > 22,...,'2014-07-09 16:12:07' > 23,...,'2014-04-09 10:20:34' > 24,...,'2014-08-08 23:44:11' > 25,...,'2014-04-09 10:20:34' > 26,...,'2014-08-09 10:00:55' > 27,...,'2014-08-09 03:18:20' > 28,...,'2014-04-09 10:20:34' > 29,...,'2014-04-09 10:20:34' > 30,...,'2014-04-09 10:20:34' > > The command above would return, > 20 > 21 > 24 > 26 > 27 > > but it should only update id 20 and 27 and should not insert any into the > local DB. I have no idea how to write it. I am now using two separate SQL > commands and using some programming to create the updates. Any help would > be greatly appreciate it. Thanks. > > josé > _______________________________________________ > 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
