[SOLVED] How can I access 2 databases in the same Query

Creating desktop or client-server database solutions?

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

Post Reply
Lagi Pittas
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 295
Joined: Mon Jun 10, 2013 1:32 pm

[SOLVED] How can I access 2 databases in the same Query

Post by Lagi Pittas » Wed Jan 17, 2018 9:46 pm

Hi

I have 2 tables , the second 1 is in a different database theuy have the same fields but table 1 has some extra fields but the key is 1 for 1 exact.

I can copy table to table if they are in the same database for example (not tested)

Code: Select all

UPDATE cust set Name=(select c2.Name from cust2 c2 where t2.id=cust.id)
How would I create a query that access to databases at the same time?

I'm thinking if there isn't a simple way to construct a memory table but wouldn't that have the same problem

Regards Lagi

EDIT

Mike Bonner in the uselist just replied to my post there.
Basically RTFM (Read The Fine Manual). The irony is I did read it ages ago and promptly forgot about "attach" about the solution. - Go to the manual first instead of googling is the lesson in this case. :oops:

Does this help? http://www.sqlitetutor ... -database/

Short version.. attach database lets you attach another db file to the
current connection. So if you have db "contacts" open, and you attach a
file AS contacts2 (which is an alias defined during the attach) then you
can address a specific table from the first db with "contacts.tablename"
and the second with "contacts2.tablename"

At which point you can most likely do something like..

INSERT INTO CONTACTS2.TABLENAME(fieldname1, fieldname2) SELECT
fieldname1, fieldname2 FROM CONTACTS.TABLENAME;

Alternatively, you might just create a copy of the table from the first db,
to create a table in the second db and then alter the table to add the
missing columns. Not sure which would be better/more efficient.

Post Reply

Return to “Databases”