Mercurial > hg > LGDataverses

annotate scripts/migration/HarvardPreMigrationDataScrub.sql @ 12:c2e2d794847f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new: add config.properties file for gazetteer
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Wed, 30 Sep 2015 13:43:54 +0200
parents a50cf11e5178
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
10
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
1 select m.id, m.TimePeriodCoveredEnd, v.study_id from metadata m, studyversion v where v.study_id = 121855 and m.id = v.metadata_id and TimePeriodCoveredEnd = '[17820000]';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
2 select m.id, m.DistributionDate, v.study_id from metadata m, studyversion v where v.study_id = 117326 and m.id = v.metadata_id and DistributionDate = '2O14';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
3 select m.id, a.date, v.study_id from metadata m, studyversion v, studyabstract a where v.study_id=47799 and m.id=v.metadata_id and m.id=a.metadata_id and a.date='201-';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
4 select m.id, m.TimePeriodCoveredEnd, v.study_id from metadata m, studyversion v where v.study_id = 88283 and m.id = v.metadata_id and TimePeriodCoveredEnd = '198x';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
5 select m.id, m.TimePeriodCoveredStart, v.study_id from metadata m, studyversion v where v.study_id = 215 and m.id = v.metadata_id and TimePeriodCoveredStart = '70s'; --should return 3 records
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
6 select m.id, a.date, v.study_id from metadata m, studyversion v, studyabstract a where v.study_id=91709 and m.id=v.metadata_id and m.id=a.metadata_id and a.date='2-13'; --should return 3 records
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
7 select m.id, a.date, v.study_id from metadata m, studyversion v, studyabstract a where v.study_id=114372 and m.id=v.metadata_id and m.id=a.metadata_id and a.date='2-14';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
8 select m.id, m.DateOfCollectionStart, m.DateOfCollectionEnd, v.study_id from metadata m, studyversion v where v.study_id = 155 and m.id = v.metadata_id and DateOfCollectionStart = '2004-01-01 to 2004-12-31' and m.DateOfCollectionEnd = '' ; -- should return 10 records
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
9
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
10 update metadata set TimePeriodCoveredEnd = '1782' from studyversion v where v.study_id = 121855 and metadata.id = v.metadata_id and TimePeriodCoveredEnd = '[17820000]';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
11 update metadata set DistributionDate = '2014' from studyversion v where v.study_id = 117326 and metadata.id = v.metadata_id and DistributionDate = '2O14';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
12 update studyabstract set date = '2010' from metadata m, studyversion v where v.study_id=47799 and m.id=v.metadata_id and m.id=studyabstract.metadata_id and studyabstract.date='201-';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
13 update metadata set TimePeriodCoveredEnd = '198?' from studyversion v where v.study_id = 88283 and metadata.id = v.metadata_id and TimePeriodCoveredEnd = '198x';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
14 update metadata set TimePeriodCoveredStart = '197?' from studyversion v where v.study_id = 215 and metadata.id = v.metadata_id and TimePeriodCoveredStart = '70s'; --should update 3 records
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
15 update studyabstract set date = '2014' from metadata m, studyversion v where v.study_id=114372 and m.id=v.metadata_id and m.id=studyabstract.metadata_id and studyabstract.date='2-14';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
16 update studyabstract set date = '2013' from metadata m, studyversion v where v.study_id=91709 and m.id=v.metadata_id and m.id=studyabstract.metadata_id and studyabstract.date='2-13'; --should update 3 records
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
17 update metadata set DateOfCollectionStart = '2004-01-01', DateOfCollectionEnd = '2004-12-31' from studyversion v where v.study_id = 155 and metadata.id = v.metadata_id and DateOfCollectionStart = '2004-01-01 to 2004-12-31' and DateOfCollectionEnd = ''; -- should update 10 records
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
18
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
19
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
20 update studyfieldvalue set strvalue='English' where metadata_id=273999 and studyfield_id=218 and strValue='English and Dutch';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
21 insert into studyfieldvalue (strvalue, metadata_id, studyfield_id, displayorder) values ('Dutch', 273999,218,1);
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
22
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
23 --Added for datasets with multiple failues 3/30
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
24 select m.id, m.TimePeriodCoveredStart, v.study_id from metadata m, studyversion v where v.study_id = 88283 and m.id = v.metadata_id and TimePeriodCoveredStart = '198x';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
25 select m.id, m.TimePeriodCoveredStart, v.study_id from metadata m, studyversion v where v.study_id = 121855 and m.id = v.metadata_id and TimePeriodCoveredStart = '[17820000]';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
26 select m.id, m.ProductionDate, v.study_id from metadata m, studyversion v where v.study_id = 121855 and m.id = v.metadata_id and ProductionDate = '[17820000]';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
27 select m.id, m.dateofdeposit, v.study_id from metadata m, studyversion v where v.study_id = 74738 and m.id = v.metadata_id and dateofdeposit = '\';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
28
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
29 update metadata set TimePeriodCoveredStart = '198?' from studyversion v where v.study_id = 88283 and metadata.id = v.metadata_id and TimePeriodCoveredStart = '198x';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
30 update metadata set ProductionDate = '1782' from studyversion v where v.study_id = 121855 and metadata.id = v.metadata_id and ProductionDate = '[17820000]';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
31 update metadata set TimePeriodCoveredStart = '1782' from studyversion v where v.study_id = 121855 and metadata.id = v.metadata_id and TimePeriodCoveredStart = '[17820000]';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
32 update metadata set dateofdeposit = '' from studyversion v where v.study_id = 74738 and metadata.id = v.metadata_id and dateofdeposit = '\';