[Treebase-guts] SF.net SVN: treebase:[1028] trunk/treebase-core/db/cleaning

[<sfr...@us...>]

Revision: 1028
          http://treebase.svn.sourceforge.net/treebase/?rev=1028&view=rev
Author:   sfrgpiel
Date:     2012-01-31 22:03:14 +0000 (Tue, 31 Jan 2012)
Log Message:
-----------
Update SQL statement to fix older TB1 citations that are still called "in prep" when they are likely to be published

Added Paths:
-----------
    trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/
    trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql

Added: trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql	                        (rev 0)
+++ trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql	2012-01-31 22:03:14 UTC (rev 1028)
@@ -0,0 +1,16 @@
+-- For old TreeBASE1 data (i.e. those with a value for tb_studyid) that have  
+-- page numbers in their citation metadata, it is reasonable to assume
+-- that these papers are published. This takes all such records that 
+-- are still called "In Prep" and toggles them to "Published"
+
+begin work;
+
+UPDATE citation SET citationstatus_id = (SELECT citationstatus_id FROM citationstatus WHERE description = 'Published')
+FROM study st JOIN citation ct USING (citation_id) 
+JOIN citationstatus cs USING (citationstatus_id)
+WHERE cs.description = 'In Prep'
+AND length(st.tb_studyid) > 1
+AND length(ct.pages) > 0 
+AND citation.citation_id = ct.citation_id;
+
+commit;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.