Update Records from Another Database

  • Thread starter Thread starter damiankinsella
  • Start date Start date
D

damiankinsella

I have used this code to append my records:
INSERT INTO EducationTbl
SELECT *
FROM EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb';

but I now need to UPDATE the records because some of the data has
altered slightly.


What I need is essentially:
UPDATE INTO EducationTbl
SELECT *
FROM EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb';


but I do not know how to correctly write this or to build it into the
query design page. Deleting the records then re-inserting them would
cause a lose of data.


Any Help? Even an example of an update from another database would be
useful.


Thanks in advance
Momo
 
To update the records in the target table from the source table you must be able
to join them on some primary key.

Assumption:
There is a PRIMARY Key (I'll call it PK)
It is ok to just update all the records

UPDATE EducationTbl as E
INNER JOIN EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb'
ON E.PK = EducationTbl.PK
SET E.FieldA = EducationTbl.FieldA,
E.FieldB = EducationTbl.FieldB
.... For every field in the table ....

If you want to only update records with changed values then you need to add a
where clause. Add it needs to handle NULLS. You can use the NZ function to do this
WHERE NZ(E.FieldA) <> NZ(EducationTbl.FieldA) OR
NZ(E.FieldB) <> NZ(EducationTbl.FieldB) OR ...


More complex would be something like the following, although it might be quicker
if there are a large number of records involved.
WHERE
(E.FieldA <> EducationTbl.FieldA
OR E.FieldA Is Null and EducationTbl.FieldA is Not Null
OR E.FieldA Is Not Null and EducationTbl.FieldA is Null)
OR
(E.FieldB <> EducationTbl.FieldB
OR E.FieldB Is Null and EducationTbl.FieldB is Not Null
OR E.FieldB Is Not Null and EducationTbl.FieldB is Null)
 
Back
Top