From ICISWiki
CHKL01A (GNPGS for derivatives):
SELECT GERMPLSM.GID, GERMPLSM.METHN, PMS_TO_GMS..METHODS.MTYPE, GERMPLSM.GNPGS, GERMPLSM.GRPLCE
FROM GERMPLSM, PMS_TO_GMS..METHODS
WHERE (((GERMPLSM.GNPGS)<>-1) AND (PMS_TO_GMS..METHODS.MTYPE='DER')) AND (GERMPLSM.METHN=PMS_TO_GMS..METHODS.MID);
CHKL01B (GNPGS for generatives):
SELECT GERMPLSM.GID, GERMPLSM.METHN, PMS_TO_GMS..METHODS.MTYPE, GERMPLSM.GNPGS, GERMPLSM.GRPLCE
FROM GERMPLSM, PMS_TO_GMS..METHODS
WHERE (((GERMPLSM.GNPGS)<0) AND (PMS_TO_GMS..METHODS.MTYPE='GEN')) AND (GERMPLSM.METHN=PMS_TO_GMS..METHODS.MID);
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));
CHKL01F2
{ Check one-step circularities }
SELECT a.GID, a.GPID1, a.GPID2
FROM GERMPLSM a WHERE EXISTS(Select * FROM GERMPLSM b Where a.GID = b.GPID2 AND a.GPID2 = b.GID AND b.GNPGS=-1 and b.GRPLCE=0)
CHKL01F3
{ Check two-step circularities }
SELECT a.GID, a.GPID1, a.GPID2
FROM GERMPLSM a WHERE EXISTS(Select * FROM GERMPLSM b Where a.GID = b.GPID2 AND
Exists(Select * FROM GERMPLSM c Where b.GID = c.GPID2 AND a.GPID2 = c.GID))
CHKL01F4
{ Check three-step circularities }
SELECT a.GID, a.GPID1, a.GPID2
FROM GERMPLSM a WHERE EXISTS(Select * FROM GERMPLSM b Where a.GID = b.GPID2 AND
Exists(Select * FROM GERMPLSM c Where b.GID = c.GPID2 AND
Exists(Select * FROM GERMPLSM d Where c.GID = d.GPID2 AND a.GPID2 = d.GID)))
CHKL01G1 (Check no replaced central GID should be referenced in the local GMS in GPID1):
SELECT GERMPLSM.GID, PMS_TO_GMS..GERMPLSM.GID, PMS_TO_GMS..GERMPLSM.GRPLCE
FROM GERMPLSM INNER JOIN PMS_TO_GMS..GERMPLSM ON GERMPLSM.GPID1 = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GRPLCE)<>0)) AND GERMPLSM.GRPLCE=0;
CHKL01G2 (Check no replaced central GID should be referenced in the local GMS in GPID2):
SELECT GERMPLSM.GID, PMS_TO_GMS..GERMPLSM.GID, PMS_TO_GMS..GERMPLSM.GRPLCE
FROM GERMPLSM INNER JOIN PMS_TO_GMS..GERMPLSM ON GERMPLSM.GPID2 = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GRPLCE)<>0)) AND GERMPLSM.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, PMS_TO_GMS..GERMPLSM.GRPLCE, CHANGES.CTO, CHANGES.CSTATUS
FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((PMS_TO_GMS..GERMPLSM.GRPLCE)<>0) AND ((CHANGES.CSTATUS)=0));
CHKL01I1
SELECT CHANGES.CTABLE, CHANGES.CFIELD, CHANGES.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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.CRECORD, CHANGES.CFROM, 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));
=====================================================================================================================
To fix a local database where the local changes to local GERMPLSM etc. was not carried out, use the following in the local database
as of 2007-09-19 JN
Equivalent to UPD09C5-UPD09D15:
UPD09C5x (Change all GRPLCE fields for local GERMPLSM records replaced by other germplasm):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)<>0));
UPDATE GERMPLSM SET GERMPLSM.GRPLCE = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)<>0));
UPD09C6x (Change all GRPLCE fields for local GERMPLSM records deleted by themselves):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)=0));
UPDATE GERMPLSM SET GERMPLSM.GRPLCE = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)=0));
UPD09D05x (GPID1):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GPID1)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID1'));
UPDATE GERMPLSM SET GERMPLSM.GPID1 = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GPID1)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID1'));
UPD09D06x (GPID2):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID2'));
UPDATE GERMPLSM SET GERMPLSM.GPID2 = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID2'));
UPD09D07x (GDATE):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GDATE'));
UPDATE GERMPLSM SET GERMPLSM.GDATE = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GDATE'));
UPD09D08x (GLOCN):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GLOCN'));
UPDATE GERMPLSM SET GERMPLSM.GLOCN = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GLOCN'));
UPD09D09x (METHN):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.METHN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='METHN') AND ((CHANGES.CTO)>=0));
UPDATE GERMPLSM SET GERMPLSM.METHN = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.METHN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='METHN') AND ((CHANGES.CTO)>=0));
UPD09D10x (GNPGS):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN GERMPLSM ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GNPGS)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GNPGS') AND ((CHANGES.CTO)>=-1));
UPDATE GERMPLSM SET GERMPLSM.GNPGS = [CHANGES].[CTO] FROM GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = GERMPLSM.GID
WHERE (((GERMPLSM.GNPGS)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GNPGS') AND ((CHANGES.CTO)>=-1));
UPD09D11x (NSTAT):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN NAMES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NSTAT)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NSTAT') AND ((CHANGES.CTO)>=0));
UPDATE NAMES SET NAMES.NSTAT = [CHANGES].[CTO] FROM NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NSTAT)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NSTAT') AND ((CHANGES.CTO)>=0));
UPD09D12x (NDATE): Jesper Nørgaard 2007-JUL-16
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN NAMES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NDATE') AND ((CHANGES.CTO)>=0));
UPDATE NAMES SET NAMES.NDATE = [CHANGES].[CTO] FROM NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NDATE') AND ((CHANGES.CTO)>=0));
UPD09D13x (NLOCN):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN NAMES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NLOCN'));
UPDATE NAMES SET NAMES.NLOCN = [CHANGES].[CTO] FROM NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NLOCN'));
UPD09D14x (NTYPE): Jesper Nørgaard 2007-JUL-16
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN NAMES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NTYPE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NTYPE') AND ((CHANGES.CTO)>=0));
UPDATE NAMES SET NAMES.NTYPE = [CHANGES].[CTO] FROM NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = NAMES.NID
WHERE (((NAMES.NTYPE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NTYPE') AND ((CHANGES.CTO)>=0));
UPD09D15x (ATYPE):
UPDATE CHANGES SET CHANGES.CSTATUS = 1 FROM CHANGES INNER JOIN ATRIBUTS ON CHANGES.CRECORD = ATRIBUTS.AID
WHERE ((ATRIBUTS.ATYPE=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='ATRIBUTS') AND ((CHANGES.CFIELD)='ATYPE') AND ((CHANGES.CTO)>=0));
UPDATE ATRIBUTS SET ATRIBUTS.ATYPE = [CHANGES].[CTO] FROM ATRIBUTS INNER JOIN CHANGES ON CHANGES.CRECORD = ATRIBUTS.AID
WHERE ((ATRIBUTS.ATYPE=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=1) AND ((CHANGES.CTABLE)='ATRIBUTS') AND ((CHANGES.CFIELD)='ATYPE') AND ((CHANGES.CTO)>=0));
to here 2007-09-19
Note that you should change all CSTATUS for GID<0 back to 0 after this operation:
UPDATE CHANGES SET CSTATUS=0 WHERE CSTATUS=1 AND CRECORD <0
This is because we will reuse the CSTATUS field to upload to the central DB, when CSTATUS must be zero to fullfill upload
... on second thought this is probably only local changes, so they would not be uploaded to central anyhow.
=====================================================================================================================
UPD03A (Set GERMPLSM.LGID = GERMPLSM.GID):
UPDATE GERMPLSM SET GERMPLSM.LGID = [germplsm].[gid];
UPD03B (corrected 2007-07-27)
CREATE TABLE MAXTABLE (MAXID INT, GID INT, NID INT, LOCID INT, AID INT, CID INT, LDID INT)
UPD03B0:
INSERT INTO MAXTABLE VALUES (1,0,0,0,0,0,0)
UPD03B1 (max GID):
UPDATE MAXTABLE SET MAXTABLE.GID = (Select MAX(PMS_TO_GMS..GERMPLSM.GID) FROM PMS_TO_GMS..GERMPLSM) WHERE MAXID=1
UPD03B2 (max NID):
UPDATE MAXTABLE SET MAXTABLE.NID = (Select MAX(PMS_TO_GMS..NAMES.NID) FROM PMS_TO_GMS..NAMES) WHERE MAXID=1
UPD03B3 (max LOCID):
UPDATE MAXTABLE SET MAXTABLE.LOCID = (Select MAX(PMS_TO_GMS..LOCATION.LOCID) FROM PMS_TO_GMS..LOCATION) WHERE MAXID=1
UPD03B4 (max AID):
UPDATE MAXTABLE SET MAXTABLE.AID = (Select MAX(PMS_TO_GMS..ATRIBUTS.AID) FROM PMS_TO_GMS..ATRIBUTS) WHERE MAXID=1
UPD03B5 (max CID):
UPDATE MAXTABLE SET MAXTABLE.CID = (Select MAX(PMS_TO_GMS..CHANGES.CID) FROM PMS_TO_GMS..CHANGES) WHERE MAXID=1
UPD03B6 (max LDID):
UPDATE MAXTABLE SET MAXTABLE.LDID = (Select MAX(PMS_TO_GMS..LOCDES.LDID) FROM PMS_TO_GMS..LOCDES) WHERE MAXID=1
UPD03C1 (new table UPDATEGID):
Drop table [UPDATEGID]
Create Table [UPDATEGID] (GID INT, LGID INT)
UPD03C2 (populate table UPDATEGID):
INSERT INTO [UPDATEGID] ( GID, LGID )
SELECT MaxTable.GID, MaxTable.GID
FROM MaxTable
UPD03D1 (new table UpdateLOC):
Drop Table UpdateLOC
CREATE TABLE UpdateLOC (LOCID INT, LLOCID INT)
UPD03D2 (populate table UpdateLOC):
INSERT INTO UpdateLOC ( LOCID, LLOCID )
SELECT MaxTable.LOCID, MaxTable.LOCID
FROM MaxTable
UPD03E1 (new table UpdCHNG):
Drop Table UPDCHNG
Create Table [UPDCHNG] (CID INT, LCID INT);
UPD03E2 (populate table UpdCHNG):
INSERT INTO UPDCHNG ( CID, LCID )
SELECT MAXTABLE.CID, MAXTABLE.CID
FROM MAXTABLE;
UPD03F1 (new table UpdNAME):
Drop Table UPDNAME
Create Table [UPDNAME] (NID INT, LNID INT);
UPD03F2 (new table UpdATRI):
Drop Table UPDATRI
Create Table [UPDATRI] (AID INT, LAID INT);
UPD03F3 (new table UpdLOCDES):
Create Table [UpdLOCDES] (LDID INT, LLDID INT);
UPD03F4 (new table UpdBIBREFS): 2007-07-31
Create Table [UpdBIBREFS] (REFID INT, LREFID INT);
UPD03F5 (new table UpdMETHODS): 2007-07-31
Create Table [UpdMETHODS] (MID INT, LMID INT);
UPD03F6 (new table UpdPROGNTRS): 2007-07-31
Create Table [UpdPROGNTRS] (PID INT, LPID INT);
UPD03F7 (new table UpdLISTNMS): 2007-07-31
Create Table [UpdLISTNMS] (LISTID INT, LLISTID INT);
UPD03F8 (new table UpdUSERS): 2007-07-31
Create Table [UpdUSERS] (USERID INT, LUSERID INT);
UPD03F9 (new table UpdUDFLDS): 2007-07-31
Create Table [UpdUDFLDS] (FLDNO INT, LFLDNO INT);
Delete all names and atributs that belong to deleted material, to not goofup SP_SCRATCH_NID_MAPPING: 2007-11-24
... or SP_SCRATCH_AID_Mapping etc.
Delete From NAMES Where Exists(Select * FROM GERMPLSM Where NAMES.GID = GERMPLSM.GID And GERMPLSM.GRPLCE <> 0)
Delete From NAMES Where GID < 0 and not exists(select * from germplsm where names.gid = germplsm.gid)
Delete From ATRIBUTS Where Exists(Select * FROM GERMPLSM Where ATRIBUTS.GID = GERMPLSM.GID And GERMPLSM.GRPLCE <> 0)
Delete From ATRIBUTS Where GID < 0 and not exists(select * from germplsm where ATRIBUTS.gid = germplsm.gid)
Delete From GERMPLSM Where GRPLCE <> 0
In SQL Server, run the following 11 corresponding stored procedures (wait with CHANGES table for later):
SP_SCRATCH_AID_Mapping
SP_SCRATCH_LDID_Mapping
SP_SCRATCH_Local_GIDS_Mapping
SP_SCRATCH_LOCID_Mapping
SP_SCRATCH_NID_Mapping
SP_SCRATCH_REFID_Mapping
SP_SCRATCH_MID_Mapping
SP_SCRATCH_PID_Mapping
SP_SCRATCH_LISTID_Mapping
SP_SCRATCH_USERID_Mapping
SP_SCRATCH_FLDNO_Mapping
Make sure that all users are present in UpdUsers, corresponding to central USERS, which will normally
just have a single record, with LUSERID = -1 and USERID = <ID> where <ID> corresponds to a user in the
PMS_TO_GMS..USERS table. But there might be more LUSERID -2, -3 etc. which will still work for the
following 3 commands
UPDATE [GERMPLSM] SET [GERMPLSM].GERMUID = [UpdUSERS].[userid] From [UpdUSERS]
WHERE (((GERMPLSM.GERMUID)=[UpdUSERS].[luserid]))
UPDATE [NAMES] SET [NAMES].NUID = [UpdUSERS].[userid] From [UpdUSERS]
WHERE (((NAMES.NUID)=[UpdUSERS].[luserid]))
UPDATE [CHANGES] SET [CHANGES].CUID = [UpdUSERS].[userid] From [UpdUSERS]
WHERE (((CHANGES.CUID)=[UpdUSERS].[luserid]))
(Atributs is handled in UPD07O).
UPD04A (generate location IDs from local to central)
INSERT INTO UpdateLOC ( LLOCID )
SELECT LOCATION.LOCID AS Expr1
FROM LOCATION
{ not applicable in SQL server, since UpdateLOC is populated with LOCID_Mapping }
UPD04B (Transfer new positive LOCID back to LOCATION table):
UPDATE LOCATION SET LOCATION.LOCID = [UpdateLOC].[LOCID] From UpdateLOC
WHERE (((LOCATION.LOCID)=[UpdateLOC].[LLOCID]))
UPD04C (Transfer new positive LOCID back to LOCDES table):
UPDATE LOCDES SET LOCDES.LOCID = [UpdateLOC].[LOCID] From UpdateLOC
WHERE (((LOCDES.LOCID)=[UpdateLOC].[LLOCID]))
UPD04D (Transfer new positive LOCID back to GERMPLSM table):
UPDATE GERMPLSM SET GERMPLSM.GLOCN = [UpdateLOC].[LOCID] From UpdateLOC
WHERE (((GERMPLSM.GLOCN)=[UpdateLOC].[LLOCID]))
UPD04E (Transfer new positive LOCID back to NAMES table):
UPDATE [NAMES] SET [NAMES].NLOCN = [UpdateLOC].[LOCID] From UpdateLOC
WHERE (((NAMES.NLOCN)=[UpdateLOC].[LLOCID]))
UPD04F (Transfer new positive LOCID back to ATRIBUTS table):
UPDATE ATRIBUTS SET ATRIBUTS.ALOCN = [UpdateLOC].[LOCID] From UpdateLOC
WHERE (((ATRIBUTS.ALOCN)=[UpdateLOC].[LLOCID]))
UPD04G (Transfer new positive LOCID back to LOCATION table for field SNL3ID):
UPDATE LOCATION SET LOCATION.SNL3ID = [UpdateLOC].[locid] From UpdateLOC
WHERE (((LOCATION.SNL3ID)=[updateloc].[llocid]))
UPD04H (Transfer new positive LOCID back to LOCATION table for field SNL2ID):
UPDATE LOCATION SET LOCATION.SNL2ID = [UpdateLOC].[locid] From UpdateLOC
WHERE (((LOCATION.SNL2ID)=[UpdateLOC].[llocid]))
UPD04I (Transfer new positive LOCID back to LOCATION table for field SNL1ID):
UPDATE LOCATION SET LOCATION.SNL1ID = [UpdateLOC].[locid] From UpdateLOC
WHERE (((LOCATION.SNL1ID)=[UpdateLOC].[llocid]))
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))
{ not applicable in SQL server, since UPDATEGID is populated with Local_GIDS_Mapping }
UPD06B1 (Put positive GIDs in GERMPLSM table according to UPDATEGID table):
UPDATE GERMPLSM SET GERMPLSM.GID = [updategid].[gid] From [UPDATEGID]
WHERE (((GERMPLSM.GID)=[updategid].[lgid]))
UPD06B2 (Put positive GIDs in PROGNTRS table according to UPDATEGID table):
UPDATE PROGNTRS SET PROGNTRS.GID = [updategid].[gid] From [UPDATEGID]
WHERE (((PROGNTRS.GID)=[updategid].[lgid]))
UPD06C1 (Put positive GIDs in GERMPLSM table for GPID1s according to UPDATEGID table):
UPDATE GERMPLSM SET GERMPLSM.GPID1 = [updategid].[gid] From [UPDATEGID]
WHERE (((GERMPLSM.GPID1)=[updategid].[lgid]))
UPD06C2 (Put positive GIDs in GERMPLSM table for GPID2s according to UPDATEGID table):
UPDATE GERMPLSM SET GERMPLSM.GPID2 = [updategid].[gid] From [UPDATEGID]
WHERE (((GERMPLSM.GPID2)=[updategid].[lgid]))
UPD07A (Update GIDs in NAMES according to UPDATEGID table):
UPDATE [NAMES] SET [NAMES].GID = [updategid].[gid] From [UPDATEGID]
WHERE (((NAMES.GID)=[updategid].[lgid]))
UPD07B (Update GIDs in ATRIBUTS according to UPDATEGID table):
UPDATE ATRIBUTS SET ATRIBUTS.GID = [updategid].[gid] From [UPDATEGID]
WHERE (((ATRIBUTS.GID)=[updategid].[lgid]) AND ((ATRIBUTS.ATYPE)<>101 Or (ATRIBUTS.ATYPE)<>102))
UPD07C (Update GIDs in LISTDATA according to UPDATEGID table):
UPDATE LISTDATA SET LISTDATA.GID = [updategid].[gid] From [UPDATEGID]
WHERE (((LISTDATA.GID)=[updategid].[lgid]));
UPD07D (Update NIDs in NAMES) 2007-07-31:
UPDATE [NAMES] SET [NAMES].NID = [updname].[nid] From [UPDNAME]
WHERE (((NAMES.NID)=[updname].[lnid]))
UPD07E (Update AIDs in ATRIBUTS) 2007-07-31:
UPDATE [ATRIBUTS] SET [ATRIBUTS].AID = [updatri].[aid] From [UPDATRI]
WHERE (((ATRIBUTS.AID)=[updatri].[laid]))
UPD07F (Update REFIDs in BIBREFS) 2007-07-31:
UPDATE [BIBREFS] SET [BIBREFS].REFID = [UpdBIBREFS].[refid] From [UpdBIBREFS]
WHERE (((BIBREFS.REFID)=[UpdBIBREFS].[lrefid]))
UPD07G (Update MIDs in METHODS) 2007-07-31:
UPDATE [METHODS] SET [METHODS].MID = [UpdMETHODS].[mid] From [UpdMETHODS]
WHERE (((METHODS.MID)=[UpdMETHODS].[lmid]))
UPD07H (Update PIDs in PROGNTRS) 2007-07-31:
UPDATE [PROGNTRS] SET [PROGNTRS].PID = [UpdPROGNTRS].[pid] From [UpdPROGNTRS]
WHERE (((PROGNTRS.PID)=[UpdPROGNTRS].[lpid]))
UPD07I (Update LISTIDs in LISTDATA) 2007-07-31:
UPDATE [LISTDATA] SET [LISTDATA].LISTID = [UpdLISTNMS].[listid] From [UpdLISTNMS]
WHERE (((LISTDATA.LISTID)=[UpdLISTNMS].[llistid]))
UPD07J (Update LISTIDs in LISTNMS) 2007-07-31:
UPDATE [LISTNMS] SET [LISTNMS].LISTID = [UpdLISTNMS].[listid] From [UpdLISTNMS]
WHERE (((LISTNMS.LISTID)=[UpdLISTNMS].[llistid]))
UPD07K (Update FLDNO in UDFLDS) 2007-07-31:
UPDATE [UDFLDS] SET [UDFLDS].FLDNO = [UpdUDFLDS].[fldno] From [UpdUDFLDS]
WHERE (((UDFLDS.FLDNO)=[UpdUDFLDS].[lfldno]))
UPD07L (Update NTYPE in NAMES using local UDFLDS) 2007-07-31:
UPDATE [NAMES] SET [NAMES].NTYPE = [UpdUDFLDS].[fldno] From [UpdUDFLDS]
WHERE (((NAMES.NTYPE)=[UpdUDFLDS].[lfldno]))
UPD07M (Update NREF in NAMES using local BIBREFS) 2007-07-31:
UPDATE [NAMES] SET [NAMES].NREF = [UpdBIBREFS].[refid] From [UpdBIBREFS]
WHERE (((NAMES.NREF)=[UpdBIBREFS].[lrefid]))
UPD07N (Update ATYPE in ATRIBUTS using local UDFLDS) 2007-07-31:
UPDATE [ATRIBUTS] SET [ATRIBUTS].ATYPE = [UpdUDFLDS].[fldno] From [UpdUDFLDS]
WHERE (((ATRIBUTS.ATYPE)=[UpdUDFLDS].[lfldno]))
UPD07O (Update AUID in ATRIBUTS using local USERS) 2007-07-31:
UPDATE [ATRIBUTS] SET [ATRIBUTS].AUID = [UpdUSERS].[userid] From [UpdUSERS]
WHERE (((ATRIBUTS.AUID)=[UpdUSERS].[luserid]))
UPD07P (Update AREF in ATRIBUTS using local BIBREFS) 2007-07-31:
UPDATE [ATRIBUTS] SET [ATRIBUTS].AREF = [UpdBIBREFS].[refid] From [UpdBIBREFS]
WHERE (((ATRIBUTS.AREF)=[UpdBIBREFS].[lrefid]))
UPD07Q (Update METHN in GERMPLSM using local METHODS) 2007-07-31:
UPDATE [GERMPLSM] SET [GERMPLSM].METHN = [UpdMETHODS].[mid] From [UpdMETHODS]
WHERE (((GERMPLSM.METHN)=[UpdMETHODS].[lmid]))
UPD07R (Update GREF in GERMPLSM using local BIBREFS) 2007-07-31:
UPDATE [GERMPLSM] SET [GERMPLSM].GREF = [UpdBIBREFS].[refid] From [UpdBIBREFS]
WHERE (((GERMPLSM.GREF)=[UpdBIBREFS].[lrefid]))
UPD07S (Update FUID in UDFLDS) 2007-08-01:
UPDATE [UDFLDS] SET [UDFLDS].FUID = [UpdUSERS].[userid] From [UpdUSERS]
WHERE (((UDFLDS.FUID)=[UpdUSERS].[luserid]))
-- Check if all these 3 requirements are fulfilled (all 4 queries 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 Exists(Select * From GERMPLSM b Where a.GID = b.GID and b.GRPLCE=0)
--UPD08C:
Select * From ATRIBUTS a Where a.GID < 0 And 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)
-- All LISTIDs in LISTDATA table should be positive
--UPD08E:
Select * From LISTDATA Where (LISTID < 0)
-- All GIDs and PIDs in PROGENITORS table should be positive
--UPD08F:
Select * From LISTNMS Where (LISTID < 0)
Revised as of 2007-08-01:
UPD09A1 (Insert locations into Access after local field LOCID has been changed to central LOCID)
INSERT INTO PMS_TO_GMS..LOCATION ( 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)) ORDER BY LOCID;
Revised as of 2007-08-01:
UPD09A2 (Insert PMS_TO_GMS..LOCDES locations into Access after local field LOCID has been changed to central LOCID)
INSERT INTO PMS_TO_GMS..LOCDES ( LDID, LOCID, DTYPE, DUID, DVAL, DDATE, DREF )
SELECT LOCDES.LDID, LOCDES.LOCID, LOCDES.DTYPE, LOCDES.DUID, LOCDES.DVAL, LOCDES.DDATE, LOCDES.DREF
FROM LOCDES ORDER BY LDID;
Revised as of 2007-08-01:
UPD09A5 (Insert PMS_TO_GMS..NAMES names after the NID has been changed to the central NID):
INSERT INTO PMS_TO_GMS..NAMES ( NID, GID, NTYPE, NSTAT, NUID, NVAL, NLOCN, NDATE, NREF )
SELECT NAMES.NID, NAMES.GID, NAMES.NTYPE, NAMES.NSTAT, NAMES.NUID, NAMES.NVAL, NAMES.NLOCN, NAMES.NDATE, NAMES.NREF
FROM [NAMES]
WHERE (((NAMES.GID)>0)) ORDER BY NID;
Additionally in SQL Server:
UPDATE PMS_TO_GMS..NAMES SET NCHANGE = 0 Where NCHANGE IS NULL;
Revised as of 2007-08-01:
UPD09A6 (Insert PMS_TO_GMS..ATRIBUTS attributes after the AID has been changed to the central AID):
INSERT INTO PMS_TO_GMS..ATRIBUTS ( AID, GID, ATYPE, AUID, AVAL, ALOCN, AREF, ADATE )
SELECT ATRIBUTS.AID, 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;
Additionally in SQL Server:
UPDATE PMS_TO_GMS..ATRIBUTS SET ATIME = 0 Where ATIME IS NULL;
UPDATE PMS_TO_GMS..ATRIBUTS SET ASTAT = 0 Where ASTAT IS NULL;
UPDATE PMS_TO_GMS..ATRIBUTS SET ACHANGE = 0 Where ACHANGE IS NULL;
Revised as of 2007-08-01:
UPD09A7 (Insert PMS_TO_GMS..GERMPLSM records after the local field GID has been changed to central GID):
INSERT INTO PMS_TO_GMS..GERMPLSM ( GID, METHN, GNPGS, GPID1, GPID2, GERMUID, LGID, GLOCN, GDATE, GREF, GRPLCE, MGID, CID, SID, GCHANGE )
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, GERMPLSM.MGID, 0, 0, 0 FROM GERMPLSM
WHERE (((GERMPLSM.GID)>0) AND ((GERMPLSM.GRPLCE)=0)) ORDER BY GID;
Additionally in SQL Server:
UPDATE PMS_TO_GMS..GERMPLSM SET MGID = 0 Where MGID IS NULL;
UPDATE PMS_TO_GMS..GERMPLSM SET CID = 0 Where CID IS NULL;
UPDATE PMS_TO_GMS..GERMPLSM SET SID = 0 Where SID IS NULL;
UPDATE PMS_TO_GMS..GERMPLSM SET GCHANGE = 0 Where GCHANGE IS NULL;
UPD09A8 (Insert PMS_TO_GMS..PROGNTRS records after the local fields GID and PID have been changed to central equivalents):
INSERT INTO PMS_TO_GMS..PROGNTRS ( GID, PNO, PID )
SELECT PROGNTRS.GID, PROGNTRS.PNO, PROGNTRS.PID
FROM PROGNTRS
WHERE (((PROGNTRS.GID)>0) AND ((PROGNTRS.PID)>0)) ORDER BY GID;
New as of 2007-08-01:
UPD09A9 (Insert PMS_TO_GMS..BIBREFS references into Access after local field REFID has been changed to central REFID)
INSERT INTO PMS_TO_GMS..BIBREFS ( REFID, PUBTYPE, PUBDATE, AUTHORS, EDITORS, ANALYT, MONOGR, SERIES, VOLUME, ISSUE,
PAGECOL, PUBLISH, PUCITY, PUCNTRY )
SELECT BIBREFS.REFID, BIBREFS.PUBTYPE, BIBREFS.PUBDATE, BIBREFS.AUTHORS, BIBREFS.EDITORS, BIBREFS.ANALYT, BIBREFS.MONOGR,
BIBREFS.SERIES, BIBREFS.VOLUME, BIBREFS.ISSUE, BIBREFS.PAGECOL, BIBREFS.PUBLISH, BIBREFS.PUCITY, BIBREFS.PUCNTRY FROM BIBREFS
ORDER BY REFID;
New as of 2007-08-01:
UPD09A10 (Insert PMS_TO_GMS..METHODS into Access after local field MID has been changed to central MID)
INSERT INTO PMS_TO_GMS..METHODS ( MID, MTYPE, MGRP, MCODE, MNAME, MDESC, MREF, MPRGN, MFPRG, MATTR, GENEQ, MUID, LMID, MDATE)
SELECT METHODS.MID, METHODS.MTYPE, METHODS.MGRP, METHODS.MCODE, METHODS.MNAME, METHODS.MDESC, METHODS.MREF, METHODS.MPRGN,
METHODS.MFPRG, METHODS.MATTR, METHODS.GENEQ, METHODS.MUID, METHODS.LMID, METHODS.MDATE FROM METHODS ORDER BY MID;
New as of 2007-08-01:
UPD09A11 (Insert PMS_TO_GMS..LISTNMS into Access after local field LISTID has been changed to central LISTID)
INSERT INTO PMS_TO_GMS..LISTNMS ( LISTID, LISTNAME, LISTDATE, LISTTYPE, LISTUID, LISTDESC, LHIERARCHY, LISTSTATUS)
SELECT LISTNMS.LISTID, LISTNMS.LISTNAME, LISTNMS.LISTDATE, LISTNMS.LISTTYPE, LISTNMS.LISTUID, LISTNMS.LISTDESC,
LISTNMS.LHIERARCHY, LISTNMS.LISTSTATUS FROM LISTNMS ORDER BY LISTID;
New as of 2007-08-01:
UPD09A12 (Insert PMS_TO_GMS..LISTDATA into Access after local field LISTID has been changed to central LISTID)
INSERT INTO PMS_TO_GMS..LISTDATA ( LISTID, GID, ENTRYID, ENTRYCD, SOURCE, DESIG, GRPNAME, LRECID, LRSTATUS)
SELECT LISTDATA.LISTID, LISTDATA.GID, LISTDATA.ENTRYID, LISTDATA.ENTRYCD, LISTDATA.SOURCE, LISTDATA.DESIG, LISTDATA.GRPNAME,
LISTDATA.LRECID, LISTDATA.LRSTATUS FROM LISTDATA ORDER BY LISTID, LRECID;
New as of 2007-08-01:
UPD09A13 (Insert PMS_TO_GMS..UDFLDS into Access after local field FLDNO has been changed to central FLDNO)
INSERT INTO PMS_TO_GMS..UDFLDS ( FLDNO, FTABLE, FTYPE, FCODE, FNAME, FFMT, FDESC, LFLDNO, FUID, FDATE, SCALEID)
SELECT UDFLDS.FLDNO, UDFLDS.FTABLE, UDFLDS.FTYPE, UDFLDS.FCODE, UDFLDS.FNAME, UDFLDS.FFMT, UDFLDS.FDESC, UDFLDS.LFLDNO,
UDFLDS.FUID, UDFLDS.FDATE, UDFLDS.SCALEID FROM UDFLDS ORDER BY FLDNO;
UPD09C4 (Change all references to GIDs in the Changes table (GID,GPID1,GPID2,PID) to their positive equivalents):
UPDATE CHANGES SET CHANGES.CTO = [UPDATEGID].[GID] From [UPDATEGID]
WHERE (((CHANGES.CTO)=[UPDATEGID].[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));
New as of 2007-11-26 (Change all references to GLOCN or NLOCN to their positive equivalent for CFROM):
UPDATE CHANGES SET CHANGES.CFROM = [UpdateLOC].[LOCID] From [UpdateLOC]
WHERE (((CHANGES.CFROM)=[UpdateLOC].[LLOCID]) AND ((CHANGES.CFIELD)='GLOCN' Or (CHANGES.CFIELD)='NLOCN') AND ((CHANGES.CRECORD)>0) AND ((CHANGES.CSTATUS)=0));
New as of 2007-11-26 (Change all references to GLOCN or NLOCN to their positive equivalent for CTO):
UPDATE CHANGES SET CHANGES.CTO = [UpdateLOC].[LOCID] From [UpdateLOC]
WHERE (((CHANGES.CTO)=[UpdateLOC].[LLOCID]) AND ((CHANGES.CFIELD)='GLOCN' Or (CHANGES.CFIELD)='NLOCN') AND ((CHANGES.CRECORD)>0) AND ((CHANGES.CSTATUS)=0));
UPD09C5 (Change all GRPLCE fields for central GERMPLSM records replaced by other germplasm):
Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)>0));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GRPLCE = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.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):
Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)=CHANGES.CRECORD));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GRPLCE = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GRPLCE)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GID') AND ((CHANGES.CTO)=CHANGES.CRECORD));
UPD09D05 (GPID1): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GPID1)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID1'));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GPID1 = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GPID1)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID1'));
UPD09D06 (GPID2): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID2'));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GPID2 = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GPID2)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GPID2'));
UPD09D07 (GDATE): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GDATE'));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GDATE = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GDATE'));
UPD09D08 (GLOCN): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GLOCN'));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GLOCN = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GLOCN'));
UPD09D09 (METHN): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.METHN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='METHN') AND ((CHANGES.CTO)>=0));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.METHN = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.METHN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='METHN') AND ((CHANGES.CTO)>=0));
UPD09D10 (GNPGS): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..GERMPLSM ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GNPGS)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GNPGS') AND ((CHANGES.CTO)>=-1));
UPDATE PMS_TO_GMS..GERMPLSM SET PMS_TO_GMS..GERMPLSM.GNPGS = [CHANGES].[CTO] FROM PMS_TO_GMS..GERMPLSM INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..GERMPLSM.GID
WHERE (((PMS_TO_GMS..GERMPLSM.GNPGS)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='GERMPLSM') AND ((CHANGES.CFIELD)='GNPGS') AND ((CHANGES.CTO)>=-1));
UPD09D11 (NSTAT): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..NAMES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NSTAT)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NSTAT') AND ((CHANGES.CTO)>=0));
UPDATE PMS_TO_GMS..NAMES SET PMS_TO_GMS..NAMES.NSTAT = [CHANGES].[CTO] FROM PMS_TO_GMS..NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NSTAT)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NSTAT') AND ((CHANGES.CTO)>=0));
UPD09D12 (NDATE): Please repeat until zero Jesper Nørgaard 2007-JUL-16
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..NAMES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NDATE') AND ((CHANGES.CTO)>=0));
UPDATE PMS_TO_GMS..NAMES SET PMS_TO_GMS..NAMES.NDATE = [CHANGES].[CTO] FROM PMS_TO_GMS..NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NDATE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NDATE') AND ((CHANGES.CTO)>=0));
UPD09D13 (NLOCN): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..NAMES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NLOCN'));
UPDATE PMS_TO_GMS..NAMES SET PMS_TO_GMS..NAMES.NLOCN = [CHANGES].[CTO] FROM PMS_TO_GMS..NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NLOCN)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTO)>0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NLOCN'));
UPD09D14 (NTYPE): Please repeat until zero Jesper Nørgaard 2007-JUL-16
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..NAMES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NTYPE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NTYPE') AND ((CHANGES.CTO)>=0));
UPDATE PMS_TO_GMS..NAMES SET PMS_TO_GMS..NAMES.NTYPE = [CHANGES].[CTO] FROM PMS_TO_GMS..NAMES INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..NAMES.NID
WHERE (((PMS_TO_GMS..NAMES.NTYPE)=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='NAMES') AND ((CHANGES.CFIELD)='NTYPE') AND ((CHANGES.CTO)>=0));
UPD09D15 (ATYPE): Please repeat until zero
UPDATE CHANGES SET CHANGES.CSTATUS = 2 FROM CHANGES INNER JOIN PMS_TO_GMS..ATRIBUTS ON CHANGES.CRECORD = PMS_TO_GMS..ATRIBUTS.AID
WHERE ((PMS_TO_GMS..ATRIBUTS.ATYPE=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=0) AND ((CHANGES.CTABLE)='ATRIBUTS') AND ((CHANGES.CFIELD)='ATYPE') AND ((CHANGES.CTO)>=0));
UPDATE PMS_TO_GMS..ATRIBUTS SET PMS_TO_GMS..ATRIBUTS.ATYPE = [CHANGES].[CTO] FROM PMS_TO_GMS..ATRIBUTS INNER JOIN CHANGES ON CHANGES.CRECORD = PMS_TO_GMS..ATRIBUTS.AID
WHERE ((PMS_TO_GMS..ATRIBUTS.ATYPE=[CHANGES].[CFROM]) AND ((CHANGES.CSTATUS)=2) AND ((CHANGES.CTABLE)='ATRIBUTS') AND ((CHANGES.CFIELD)='ATYPE') AND ((CHANGES.CTO)>=0));
Revised as of 2007-08-01:
UPD09E3:
SQL Server, run equivalent stored procedure SP_SCRATCH_CID_Mapping
INSERT INTO UPDCHNG ( LCID )
SELECT CHANGES.CID
FROM CHANGES
WHERE (((CHANGES.CSTATUS)=2) AND ((CHANGES.CRECORD)>0))
ORDER BY CHANGES.CID;
UPD09E4:
UPDATE CHANGES SET CHANGES.CID = [UPDCHNG].[CID], CHANGES.CSTATUS = [UPDCHNG].[LCID] FROM CHANGES INNER JOIN UPDCHNG ON CHANGES.CID = UPDCHNG.LCID;
UPD09E5:
INSERT INTO PMS_TO_GMS..CHANGES ( 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));
