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!

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

Thanks for the reply. unfortunatley, PSQL 2000i does not like this syntax.

What error does it give?
Also, what's the exact SQL statement you are using?

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

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

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