Mercurial > hg > LGDataverses

comparison scripts/migration/HarvardPreMigrationDataScrub.sql @ 10:a50cf11e5178

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rewrite LGDataverse completely upgrading to dataverse4.0
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Tue, 08 Sep 2015 17:00:21 +0200
parents
children
comparison
equal deleted inserted replaced
9:5926d6419569 10:a50cf11e5178
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]';
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';
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-';
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';
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
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
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';
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
9
10 update metadata set TimePeriodCoveredEnd = '1782' from studyversion v where v.study_id = 121855 and metadata.id = v.metadata_id and TimePeriodCoveredEnd = '[17820000]';
11 update metadata set DistributionDate = '2014' from studyversion v where v.study_id = 117326 and metadata.id = v.metadata_id and DistributionDate = '2O14';
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-';
13 update metadata set TimePeriodCoveredEnd = '198?' from studyversion v where v.study_id = 88283 and metadata.id = v.metadata_id and TimePeriodCoveredEnd = '198x';
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
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';
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
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
18
19
20 update studyfieldvalue set strvalue='English' where metadata_id=273999 and studyfield_id=218 and strValue='English and Dutch';
21 insert into studyfieldvalue (strvalue, metadata_id, studyfield_id, displayorder) values ('Dutch', 273999,218,1);
22
23 --Added for datasets with multiple failues 3/30
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';
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]';
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]';
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 = '\';
28
29 update metadata set TimePeriodCoveredStart = '198?' from studyversion v where v.study_id = 88283 and metadata.id = v.metadata_id and TimePeriodCoveredStart = '198x';
30 update metadata set ProductionDate = '1782' from studyversion v where v.study_id = 121855 and metadata.id = v.metadata_id and ProductionDate = '[17820000]';
31 update metadata set TimePeriodCoveredStart = '1782' from studyversion v where v.study_id = 121855 and metadata.id = v.metadata_id and TimePeriodCoveredStart = '[17820000]';
32 update metadata set dateofdeposit = '' from studyversion v where v.study_id = 74738 and metadata.id = v.metadata_id and dateofdeposit = '\';