Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    10

    Unanswered: Update across different databases

    I am using Pervasive.SQL 2000i. I have 2 databases, say db1 abd db2. In db1 I have a table tbl1 and in db2 i have a table with the same structure but different data. (db2 is a copy of db1). The structure is as follows:

    db1
    customer totalytd totallyr


    db2
    customer totalytd totallyr

    The customer fields have the same data. I need to update 'totallyr' in db1 with the values in 'totalytd' in db2 for each customer. Can someone help me with the update statement for this? Thanks in advance!

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I don't know if it'll work with PSQL 2000i but in V8 (and later) you can add the database name (not DSN name) before the table name. For example:
    select db1.table1.field1 from db1.table1
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jun 2005
    Posts
    10
    Thanks for the reply. unfortunatley, PSQL 2000i does not like this syntax.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What error does it give?
    Also, what's the exact SQL statement you are using?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Jun 2005
    Posts
    10
    This is the error:

    ODBC Error: SQLSTATE = 37000, Native error code = 0
    Syntax Error: UPDATE HPFYE.RM00103
    SET HPFYE.RM00103.<< ??? >>TTLSLLYR = HPFSAMPLE.RM00103.TTLSLYTD
    WHERE HPFYE.RM00103.CUSTNMBR = HPFSAMPLE.RM00103.CUSTNMBR

    This is the statement:

    UPDATE HPFYE.RM00103
    SET HPFYE.RM00103.TTLSLLYR = HPFSAMPLE.RM00103.TTLSLYTD
    WHERE HPFYE.RM00103.CUSTNMBR = HPFSAMPLE.RM00103.CUSTNMBR

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Just to confirm:
    TTLSLLYR is a field.
    RM00103 is the table name
    and
    HPFYE is the database name (not ODBC DSN but Pervasive Database name).

    Is that correct?
    Does the following statement work:
    select HPFYE.RM00103.TTLSLLYR from HPFSAMPLE.RM00103
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Jun 2005
    Posts
    10
    Yes, this is correct.

    The statement does not work: It gives an error:
    ODBC Error: SQLSTATE = 37000, Native error code = 0
    Syntax Error: select HPFYE.RM00103.<< ??? >>TTLSLLYR from HPFSAMPLE.RM00103

    This works though:
    SELECT RM00103.TTLSLLYR FROM HPFSAMPLE.RM00103

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •