Updating 3 tables based on the ProjID

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Updating 3 tables based on the ProjID

jose isaias cabrera-3

Greetings!

I have OpenProjects, OpenSubProjects, OpenJobs and each have an unique key called ProjID. I want to update a backup for each based on

XtraB != client.LSOpenProjects.XtraB

So, is there a way of combining these three INSERTS into one?

ATTACH 'L:\Data\OpenJobsTool\Shared.DB.AllOpenProjs.db' AS client;

BEGIN;
  INSERT OR REPLACE INTO client.LSOpenProjects
    SELECT * FROM LSOpenProjects
      WHERE ProjID IN
      (
        SELECT ProjID from LSOpenProjects
        WHERE
          XtraB != client.LSOpenProjects.XtraB
      );
  INSERT OR REPLACE INTO client.LSOpenSubProjects
    SELECT * FROM LSOpenSubProjects
      WHERE ProjID IN
      (
        SELECT ProjID from LSOpenProjects
        WHERE
          XtraB != client.LSOpenProjects.XtraB
      );
  INSERT OR REPLACE INTO client.LSOpenJobs
    SELECT * FROM LSOpenJobs
      WHERE ProjID IN
      (
        SELECT ProjID from LSOpenProjects
        WHERE
          XtraB != client.LSOpenProjects.XtraB
      );
END;

thanks for your help.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating 3 tables based on the ProjID

Simon Slavin-3


On 22 Aug 2017, at 8:45pm, jose isaias cabrera <[hidden email]> wrote:

> So, is there a way of combining these three INSERTS into one?

You may be able to combine the three SELECTs into one using UNION.  I don’t know if it’ll work if they have different columns.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating 3 tables based on the ProjID

jose isaias cabrera-3

On Tuesday, August 22, 2017 4:47 PM Simon Slavin wrote...
On 22 Aug 2017, at 8:45pm, jose isaias cabrera <[hidden email]>
wrote:

>> So, is there a way of combining these three INSERTS into one?

> You may be able to combine the three SELECTs into one using UNION.  I don’t
> know
> if it’ll work if they have different columns.

LSOpenProject has the same columns as client.LSOpenProjects.  The same is
true for LSOpenSubProject and LSOpenJobs.  I am just trying to save time
with the inner most SELECT ProjID.  I guess I can do a SELECT ProjID and
then create a combined INSERT programmatically.  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating 3 tables based on the ProjID

Simon Slavin-3


On 22 Aug 2017, at 11:08pm, jose isaias cabrera <[hidden email]> wrote:

> LSOpenProject has the same columns as client.LSOpenProjects.  The same is true for LSOpenSubProject and LSOpenJobs.  I am just trying to save time with the inner most SELECT ProjID.

I made a mistake.  Your original code inserts the same rows into three different tables.  I thought it inserted three different sets of rows into one table.

So it depends on how much data you have in your tables but you might do something like

CREATE TEMPORARY TABLE newRows (<whatever>);

That at the beginning of your program.  Then when you backup ...

INSERT INTO newRows SELECT * FROM LSOpenProjects
     WHERE ProjID IN
     (
       SELECT ProjID from LSOpenProjects
       WHERE
         XtraB != client.LSOpenProjects.XtraB
     );

INSERT OR REPLACE INTO client.LSOpenProjects SELECT * FROM newRows;
INSERT OR REPLACE INTO client.LSOpenSubProjects SELECT * FROM newRows;
INSERT OR REPLACE INTO client.LSOpenJobs SELECT * FROM newRows;

DELETE * FROM newRows;



Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating 3 tables based on the ProjID

jose isaias cabrera-3

Muchas gracias.


-----Original Message-----
From: Simon Slavin
Sent: Tuesday, August 22, 2017 6:32 PM
To: SQLite mailing list
Subject: Re: [sqlite] Updating 3 tables based on the ProjID



On 22 Aug 2017, at 11:08pm, jose isaias cabrera <[hidden email]>
wrote:

> LSOpenProject has the same columns as client.LSOpenProjects.  The same is
> true for LSOpenSubProject and LSOpenJobs.  I am just trying to save time
> with the inner most SELECT ProjID.

I made a mistake.  Your original code inserts the same rows into three
different tables.  I thought it inserted three different sets of rows into
one table.

So it depends on how much data you have in your tables but you might do
something like

CREATE TEMPORARY TABLE newRows (<whatever>);

That at the beginning of your program.  Then when you backup ...

INSERT INTO newRows SELECT * FROM LSOpenProjects
     WHERE ProjID IN
     (
       SELECT ProjID from LSOpenProjects
       WHERE
         XtraB != client.LSOpenProjects.XtraB
     );

INSERT OR REPLACE INTO client.LSOpenProjects SELECT * FROM newRows;
INSERT OR REPLACE INTO client.LSOpenSubProjects SELECT * FROM newRows;
INSERT OR REPLACE INTO client.LSOpenJobs SELECT * FROM newRows;

DELETE * FROM newRows;



Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users