Updating records from a SELECT that exists in the table

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Updating records from a SELECT that exists in the table

jose isaias cabrera

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating records from a SELECT that exists in the table

YONGIL JANG
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 <[hidden email]>:

>
> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users