Access source Codes for GMS Setup

From ICISWiki

Contents 1 Check Local Database integrity 2 Prepare local update environment 3 Update Locations 4 Extract all local germplasm without local references 5 Update all remaining local germplasm references 6 Check update 7 Execute central changes and restore the local database

Check Local Database integrity

CHKL01A (GNPGS for derivatives):

SELECT GERMPLSM.GID, GERMPLSM.METHN, METHODS1.MTYPE AS Expr1, GERMPLSM.GNPGS, GERMPLSM.GRPLCE
FROM GERMPLSM, METHODS1
WHERE (((GERMPLSM.GNPGS)<>-1) AND (([METHODS1].[MTYPE])="DER"));

CHKL01B (GNPGS for generatives):

SELECT GERMPLSM.GID, GERMPLSM.METHN, METHODS1.MTYPE AS Expr1, GERMPLSM.GNPGS, GERMPLSM.GRPLCE
FROM GERMPLSM, METHODS1
WHERE (((GERMPLSM.GNPGS)<0) AND (([METHODS1].[MTYPE])="GEN"));

CHKL01C (Check GPID1 <> GID for any record):

SELECT GERMPLSM.GID
FROM GERMPLSM
WHERE (((GERMPLSM.GID)=[GERMPLSM].[GPID1]));

CHKL01D (Check GPID2 <> GID for any record):

SELECT GERMPLSM.GID
FROM GERMPLSM
WHERE (((GERMPLSM.GID)=[GERMPLSM].[GPID2]));

CHKL01E (Check that all derivatives with known source (GPID2 <> 0) have known group (GPID1 <> 0) ):

SELECT GERMPLSM.GID, GERMPLSM.GNPGS, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM GERMPLSM
WHERE (((GERMPLSM.GNPGS)=-1) AND ((GERMPLSM.GPID1)=0) AND ((GERMPLSM.GPID2)<>0));

CHKL01F1

SELECT GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM GERMPLSM
WHERE (((GERMPLSM.GNPGS)=-1) AND ((GERMPLSM.GRPLCE)=0));

CHKL01F2

SELECT CHKL01F1.GID, GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM CHKL01F1 INNER JOIN GERMPLSM ON CHKL01F1.GPID2 = GERMPLSM.GID
WHERE (((CHKL01F1.GID)=[GERMPLSM].[GID]));

CHKL01F3

SELECT CHKL01F1.GID, GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM CHKL01F1 INNER JOIN GERMPLSM ON CHKL01F1.GPID2 = GERMPLSM.GID;

CHKL01F4

SELECT CHKL01F3.CHKL01F1.GID, GERMPLSM.GID, GERMPLSM.GPID1, GERMPLSM.GPID2
FROM CHKL01F3 INNER JOIN GERMPLSM ON CHKL01F3.GPID2 = GERMPLSM.GID
WHERE (((CHKL01F3.CHKL01F1.GID)=[GERMPLSM].[GID]));

CHKL01G1 (Check no replaced central GID should be referenced in the local GMS):

SELECT GERMPLSM.GID, GERMPLSM1.GID, GERMPLSM1.GRPLCE
FROM GERMPLSM INNER JOIN GERMPLSM1 ON GERMPLSM.GPID1 = GERMPLSM1.GID
WHERE (((GERMPLSM1.GRPLCE)<>0));

CHKL01G2 (Check no replaced central GID should be referenced in the local GMS):

SELECT GERMPLSM.GID, GERMPLSM1.GID, GERMPLSM1.GRPLCE
FROM GERMPLSM INNER JOIN GERMPLSM1 ON GERMPLSM.GPID2 = GERMPLSM1.GID
WHERE (((GERMPLSM1.GRPLCE)<>0));

CHKL01H (Check that no replacements in the CHANGES table have already been replaced or deleted in the central database):

SELECT CHANGES.CTABLE, CHANGES.CFIELD, GERMPLSM1.GRPLCE, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GID") AND ((GERMPLSM1.GRPLCE)<>0)
AND ((CHANGES.CSTATUS)=0));

CHKL01I1

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLASM") AND ((CHANGES.CFIELD)="GID") AND
((CHANGES.CTO)<>[GERMPLSM].[GRPLCE]) AND ((CHANGES.CSTATUS)=0));

CHKL01I2

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID1") AND
((CHANGES.CTO)<>[GERMPLSM].[GPID1]) AND ((CHANGES.CSTATUS)=0));

CHKL01I3

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID2") AND
((CHANGES.CTO)<>[GERMPLSM].[GPID2]) AND ((CHANGES.CSTATUS)=0));

CHKL01I4

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GDATE") AND
((CHANGES.CTO)<>[GERMPLSM].[GDATE]) AND ((CHANGES.CSTATUS)=0));

CHKL01I5

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GLOCN") AND
((CHANGES.CTO)<>[GERMPLSM].[GLOCN]) AND ((CHANGES.CSTATUS)=0));

CHKL01I6

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="METHN") AND
((CHANGES.CTO)<>[GERMPLSM].[METHN]) AND ((CHANGES.CSTATUS)=0));

CHKL01I7

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GNPGS") AND
((CHANGES.CTO)<>[GERMPLSM].[GNPGS]) AND ((CHANGES.CSTATUS)=0));

CHKL01I8

SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GREF") AND
((CHANGES.CTO)<>[GERMPLSM].[GREF]) AND ((CHANGES.CSTATUS)=0));

Prepare local update environment

UPD03A (Set GERMPLSM.LGID = GERMPLSM.GID):

UPDATE GERMPLSM SET GERMPLSM.LGID = [germplsm].[gid];

UPD03B (corrected 2007-07-27)

CREATE TABLE MAXTABLE (MAXID LONG, GID LONG, NID LONG, LOCID LONG, AID LONG, CID LONG, LDID LONG)

UPD03B0:

INSERT INTO MAXTABLE VALUES (1,0,0,0,0,0,0)

UPD03B1 (max GID):

UPDATE MAXTABLE SET MAXTABLE.GID = (Select MAX(GERMPLSM1.GID) FROM GERMPLSM1) WHERE MAXID=1

UPD03B2 (max NID):

UPDATE MAXTABLE SET MAXTABLE.NID = (Select MAX(NAMES1.NID) FROM NAMES1) WHERE MAXID=1

UPD03B3 (max LOCID):

UPDATE MAXTABLE SET MAXTABLE.LOCID = (Select MAX(LOCATION1.LOCID) FROM LOCATION1) WHERE MAXID=1

UPD03B4 (max AID):

UPDATE MAXTABLE SET MAXTABLE.AID = (Select MAX(ATRIBUTS1.AID) FROM ATRIBUTS1) WHERE MAXID=1

UPD03B5 (max CID):

UPDATE MAXTABLE SET MAXTABLE.CID = (Select MAX(CHANGES1.CID) FROM CHANGES1) WHERE MAXID=1

UPD03B6 (max LDID):

UPDATE MAXTABLE SET MAXTABLE.LDID = (Select MAX(LOCDES1.LDID) FROM LOCDES1) WHERE MAXID=1

UPD03C1 (new table UPDATEGID):

Create Table [UPDATEGID]  (GID Long, LGID Long)

UPD03C2 (populate table UPDATEGID):

INSERT INTO [UPDATEGID] ( GID, LGID )
SELECT MaxTable.GID, MaxTable.GID
FROM MaxTable

UPD03D1 (new table UpdateLOC):

CREATE TABLE UpdateLOC (LOCID LONG, LLOCID LONG)

UPD03D2 (populate table UpdateLOC):

INSERT INTO UpdateLOC ( LOCID, LLOCID )
SELECT MaxTable.LOCID, MaxTable.LOCID
FROM MaxTable

UPD03E1 (new table UpdCHNG):

Create Table [UPDCHNG]  (CID Counter, LCID Long);

UPD03E2 (populate table UpdCHNG):

INSERT INTO UPDCHNG ( CID, LCID )
SELECT MAXTABLE.CID, MAXTABLE.CID
FROM MAXTABLE;

UPD03F1 (new table UpdNAME):

Create Table [UPDNAME]  (NID Long, LNID Long);

UPD03F2 (populate table UpdNAME):

INSERT INTO UPDNAME ( NID, LNID )
SELECT MAXTABLE.NID, MAXTABLE.NID
FROM MAXTABLE;

UPD03G1 (new table UpdATRIBUTS):

Create Table [UPDATRIBUTS]  (AID Long, LAID Long);

UPD03G2 (populate table UpdATRIBUTS):

INSERT INTO UPDATRIBUTS ( AID, LAID )
SELECT MAXTABLE.AID, MAXTABLE.AID
FROM MAXTABLE;

Update Locations

UPD04A (generate location IDs from local to central)

INSERT INTO UpdateLOC ( LLOCID )
SELECT LOCATION.LOCID AS Expr1
FROM LOCATION

UPD04B (Transfer new positive LOCID back to LOCATION table):

UPDATE LOCATION, UpdateLOC SET LOCATION.LOCID = [UpdateLOC].[LOCID]
WHERE (((LOCATION.LOCID)=[UpdateLOC].[LLOCID]))

UPD04C (Transfer new positive LOCID back to LOCDES table):

UPDATE UpdateLOC, LOCDES SET LOCDES.LOCID = [UpdateLOC].[LOCID]
WHERE (((LOCDES.LOCID)=[UpdateLOC].[LLOCID]))

UPD04D (Transfer new positive LOCID back to GERMPLSM table):

UPDATE UpdateLOC, GERMPLSM SET GERMPLSM.GLOCN = [UpdateLOC].[LOCID]
WHERE (((GERMPLSM.GLOCN)=[UpdateLOC].[LLOCID]))

UPD04E (Transfer new positive LOCID back to NAMES table):

UPDATE UpdateLOC, [NAMES] SET [NAMES].NLOCN = [UpdateLOC].[LOCID]
WHERE (((NAMES.NLOCN)=[UpdateLOC].[LLOCID]))

UPD04F (Transfer new positive LOCID back to ATRIBUTS table):

UPDATE UpdateLOC, ATRIBUTS SET ATRIBUTS.ALOCN = [UpdateLOC].[LOCID]
WHERE (((ATRIBUTS.ALOCN)=[UpdateLOC].[LLOCID]))

UPD04G (Transfer new positive LOCID back to LOCATION table for field SNL3ID):

UPDATE LOCATION, UpdateLOC SET LOCATION.SNL3ID = [updateloc].[locid]
WHERE (((LOCATION.SNL3ID)=[updateloc].[llocid]))

UPD04H (Transfer new positive LOCID back to LOCATION table for field SNL2ID):

UPDATE LOCATION, UpdateLOC SET LOCATION.SNL2ID = [updateloc].[locid]
WHERE (((LOCATION.SNL2ID)=[updateloc].[llocid]))

UPD04I (Transfer new positive LOCID back to LOCATION table for field SNL1ID):

UPDATE LOCATION, UpdateLOC SET LOCATION.SNL1ID = [updateloc].[locid]
WHERE (((LOCATION.SNL1ID)=[updateloc].[llocid]))

Extract all local germplasm without local references

UPD06A (Generate positive GIDs for GERMPLSM table into table UPDATEGID)

INSERT INTO [UPDATEGID] ( LGID )
SELECT GERMPLSM.GID
FROM GERMPLSM
WHERE (((GERMPLSM.GID)<0) AND ((GERMPLSM.GPID1)>=0) AND ((GERMPLSM.GPID2)>=0) AND ((GERMPLSM.GRPLCE)=0))

UPD06B1 (Put positive GIDs in GERMPLSM table according to UPDATEGID table):

UPDATE GERMPLSM, [UPDATEGID] SET GERMPLSM.GID = [updategid].[gid]
WHERE (((GERMPLSM.GID)=[updategid].[lgid]))

UPD06B2 (Put positive GIDs in PROGNTRS table according to UPDATEGID table):

UPDATE [UPDATEGID], PROGNTRS SET PROGNTRS.GID = [updategid].[gid]
WHERE (((PROGNTRS.GID)=[updategid].[lgid]))

UPD06C1 (Put positive GIDs in GERMPLSM table for GPID1s according to UPDATEGID table):

UPDATE GERMPLSM, [UPDATEGID] SET GERMPLSM.GPID1 = [updategid].[gid]
WHERE (((GERMPLSM.GPID1)=[updategid].[lgid]))

UPD06C2 (Put positive GIDs in GERMPLSM table for GPID2s according to UPDATEGID table):

UPDATE GERMPLSM, [UPDATEGID] SET GERMPLSM.GPID2 = [updategid].[gid]
WHERE (((GERMPLSM.GPID2)=[updategid].[lgid]))

--Note that query UPD06A *must* be run again after running UPD06C1 and UPD06C2!! 2007-07-29

--UPD06C3 (Put positive GIDs in GERMPLSM table for GPID2s according to UPDATEGID table):
--UPDATE [UPDATEGID], PROGNTRS SET PROGNTRS.PID = [updategid].[gid]
--WHERE (((PROGNTRS.PID)=[updategid].[lgid]))
-- Actually UPD06C3 is identical to UPD06B2 !!

Update all remaining local germplasm references

UPD07A (Update GIDs in NAMES according to UPDATEGID table):

UPDATE [NAMES], [UPDATEGID] SET [NAMES].GID = [updategid].[gid]
WHERE (((NAMES.GID)=[updategid].[lgid]))

UPD07B (Update GIDs in ATRIBUTS according to UPDATEGID table):

UPDATE [UPDATEGID], ATRIBUTS SET ATRIBUTS.GID = [updategid].[gid]
WHERE (((ATRIBUTS.GID)=[updategid].[lgid]) AND ((ATRIBUTS.ATYPE)<>101 Or (ATRIBUTS.ATYPE)<>102))

UPD07C (Update GIDs in LISTDATA according to UPDATEGID table):

UPDATE LISTDATA, [UPDATEGID] SET LISTDATA.GID = [updategid].[gid]
WHERE (((LISTDATA.GID)=[updategid].[lgid]));

Check update

Check if all these 3 requirements are fulfilled (all 3 must return 0 records):
There should be no records in GERMPLSM with negative GIDs and GRPLCE = 0.
UPD08A:

Select * From GERMPLSM Where GRPLCE=0 And GID < 0

The only negative GIDs in the NAMES and ATRIBUTS table should belong to replaced or deleted records.
UPD08B:

Select * From NAMES a Where GID < 0 And NSTAT <> 9 And Not Exists
(Select * From GERMPLSM b Where a.GID = b.GID and b.GRPLCE<>0)

UPD08C:

Select * From ATRIBUTS a Where a.GID < 0 And Not Exists
(Select * From GERMPLSM b Where a.GID = b.GID and b.GRPLCE<>0)

All GIDs and PIDs in PROGENITORS table should be positive.
UPD08D:

Select * From PROGNTRS Where (GID < 0 Or PID < 0)

Execute central changes and restore the local database

UPD09A1 (Insert locations into Access after local field LOCID has been changed to central LOCID)

INSERT INTO LOCATION1 ( LOCID, LTYPE, NLLP, LNAME, LABBR, SNL3ID, SNL2ID, SNL1ID, CNTRYID, LRPLCE )
SELECT LOCATION.LOCID, LOCATION.LTYPE, LOCATION.NLLP, LOCATION.LNAME, LOCATION.LABBR, LOCATION.SNL3ID, LOCATION.SNL2ID, LOCATION.SNL1ID, LOCATION.CNTRYID, LOCATION.LRPLCE
FROM LOCATION
WHERE (((LOCATION.LOCID)>0));

UPD09A2 (Insert LOCDES1 locations into Access after local field LOCID has been changed to central LOCID)

INSERT INTO LOCDES1 ( LOCID, DTYPE, DUID, DVAL, DDATE, DREF )
SELECT LOCDES.LOCID, LOCDES.DTYPE, LOCDES.DUID, LOCDES.DVAL, LOCDES.DDATE, LOCDES.DREF
FROM LOCDES;

UPD09A5 (Insert NAMES1 names using the autonumeric quality of the central NID):

INSERT INTO NAMES1 ( GID, NTYPE, NSTAT, NUID, NVAL, NLOCN, NDATE, NREF )
SELECT NAMES.GID, NAMES.NTYPE, NAMES.NSTAT, NAMES.NUID, NAMES.NVAL, NAMES.NLOCN, NAMES.NDATE, NAMES.NREF
FROM [NAMES]
WHERE (((NAMES.GID)>0));

UPD09A6 (Insert ATRIBUTS1 attributes using the autonumeric quality of the central AID):

INSERT INTO ATRIBUTS1 ( GID, ATYPE, AUID, AVAL, ALOCN, AREF, ADATE )
SELECT ATRIBUTS.GID, ATRIBUTS.ATYPE, ATRIBUTS.AUID, ATRIBUTS.AVAL, ATRIBUTS.ALOCN, ATRIBUTS.AREF, ATRIBUTS.ADATE
FROM ATRIBUTS
WHERE (((ATRIBUTS.GID)>0) AND ((ATRIBUTS.ATYPE)<>101 And (ATRIBUTS.ATYPE)<>102))
ORDER BY ATRIBUTS.AID DESC;

UPD09A7 (Insert GERMPLSM1 records after the local field GID has been changed to central GID):

INSERT INTO GERMPLSM1 ( GID, METHN, GNPGS, GPID1, GPID2, GERMUID, LGID, GLOCN, GDATE, GREF, GRPLCE )
SELECT GERMPLSM.GID, GERMPLSM.METHN, GERMPLSM.GNPGS, GERMPLSM.GPID1, GERMPLSM.GPID2, GERMPLSM.GERMUID AS Expr1, GERMPLSM.LGID, GERMPLSM.GLOCN, GERMPLSM.GDATE, GERMPLSM.GREF, GERMPLSM.GRPLCE
FROM GERMPLSM
WHERE (((GERMPLSM.GID)>0) AND ((GERMPLSM.GRPLCE)=0));

UPD09A8 (Insert PROGNTRS1 records after the local fields GID and PID have been changed to central equivalents):

INSERT INTO PROGNTRS1 ( GID, PNO, PID )
SELECT PROGNTRS.GID, PROGNTRS.PNO, PROGNTRS.PID
FROM PROGNTRS
WHERE (((PROGNTRS.GID)>0) AND ((PROGNTRS.PID)>0));

UPD09C4 (Change all references to GIDs in the Changes table (GID,GPID1,GPID2,PID) to their positive equivalents):

UPDATE CHANGES, [UPDATE] SET CHANGES.CTO = [UPDATE].[GID]
WHERE (((CHANGES.CTO)=[UPDATE].[LGID]) AND ((CHANGES.CFIELD)="GID" Or (CHANGES.CFIELD)="GPID1" Or (CHANGES.CFIELD)="GPID2" Or (CHANGES.CFIELD)="PID") AND ((CHANGES.CRECORD)>0) AND ((CHANGES.CSTATUS)=0));

UPD09C5 (Change all GRPLCE fields for central GERMPLSM records replaced by other germplasm):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GRPLCE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GRPLCE)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GID") AND ((CHANGES.CTO)>0));

UPD09C6 (Change all GRPLCE fields for central GERMPLSM records deleted by themselves):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GRPLCE = [CHANGES].[CRECORD], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GRPLCE)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GID") AND ((CHANGES.CTO)=0));

UPD09D05 (GPID1):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GPID1 = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GPID1)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID1") AND ((CHANGES.CTO)>=0));

UPD09D06 (GPID2):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GPID2 = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GPID2") AND ((CHANGES.CTO)>=0));

UPD09D07 (GDATE):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GDATE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GDATE") AND ((CHANGES.CTO)>=0));

UPD09D08 (GLOCN):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GLOCN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GLOCN") AND ((CHANGES.CTO)>=0));

UPD09D09 (METHN):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.METHN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.METHN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="METHN") AND ((CHANGES.CTO)>=0));

UPD09D10 (GNPGS):

UPDATE CHANGES INNER JOIN GERMPLSM1 ON CHANGES.CRECORD = GERMPLSM1.GID SET GERMPLSM1.GNPGS = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((GERMPLSM1.GNPGS)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="GERMPLSM") AND ((CHANGES.CFIELD)="GNPGS") AND ((CHANGES.CTO)>=-1));

UPD09D11 (NSTAT): (139)

UPDATE CHANGES INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID SET NAMES1.NSTAT = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NSTAT)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NSTAT") AND ((CHANGES.CTO)>=0));

UPD09D12 (NDATE): Jesper Nørgaard 2007-JUL-16

UPDATE CHANGES INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID SET NAMES1.NDATE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NDATE"));

UPD09D13 (NLOCN):

UPDATE CHANGES INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID SET NAMES1.NLOCN = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NLOCN") AND ((CHANGES.CTO)>=0));

UPD09D14 (NTYPE): Jesper Nørgaard 2007-JUL-16

UPDATE CHANGES INNER JOIN NAMES1 ON CHANGES.CRECORD = NAMES1.NID SET NAMES1.NTYPE = [CHANGES].[CTO], CHANGES.CSTATUS = 2
WHERE (((NAMES1.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="NAMES") AND ((CHANGES.CFIELD)="NTYPE"));

UPD09D15:

UPDATE CHANGES INNER JOIN ATRIBUTS1 ON CHANGES.CRECORD = ATRIBUTS1.AID SET ATRIBUTS1.ATYPE = [changes].[cto], CHANGES.CSTATUS = 2
WHERE (((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)="atributs") AND ((CHANGES.CFIELD)="atype") AND ((CHANGES.CTO)>=0));

UPD09E3:

INSERT INTO UPDCHNG ( LCID )
SELECT CHANGES.CID
FROM CHANGES
WHERE (((CHANGES.CSTATUS)=2) AND ((CHANGES.CRECORD)>0))
ORDER BY CHANGES.CID DESC;

UPD09E4:

UPDATE CHANGES INNER JOIN UPDCHNG ON CHANGES.CID = UPDCHNG.LCID SET CHANGES.CID = [UPDCHNG].[CID], CHANGES.CSTATUS = [UPDCHNG].[LCID];

UPD09E5:

INSERT INTO CHANGES1 ( CID, CTABLE, CFIELD, CRECORD, CFROM, CTO, CDATE, CTIME, CGROUP, CREF, CSTATUS, CDESC )
SELECT CHANGES.CID, CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CRECORD, CHANGES.CFROM, CHANGES.CTO, CHANGES.CDATE, CHANGES.CTIME, CHANGES.CGROUP, CHANGES.CREF, CHANGES.CSTATUS, CHANGES.CDESC
FROM CHANGES
WHERE (((CHANGES.CID)>0));