Database-conversion.md

@@ -61,20 +61,8 @@ and
 cdli_db_old.fulltrans.wholetext = cdli_db.inscriptions.atf
 ```
 
-update is_latest
 
-```sql
-update cdli_db.inscriptions
-join cdli_db_old.revhistories on 
-cdli_db_old.revhistories.id = cdli_db.inscriptions.id
-set cdli_db.inscriptions.is_latest = 1
-where
-cdli_db.inscriptions.id = (SELECT t1.id
-FROM cdli_db_old.revhistories t1
-WHERE t1.mod_date = (SELECT MAX(t2.mod_date)
-FROM cdli_db_old.revhistories t2
-WHERE t2.object_id = t1.object_id));
-```
+
 
 Prepare or update tmp author and credits table:   
   
@@ -231,21 +219,32 @@ AND cdli_db_tmp.inscriptions_credits_view3.author_id_3 IS NOT NULL;
 ## 1.3 update inscriptions
 
 ## 1.3.1 Set is_latest"
-match fulltrans.wholetext with inscriptions.atf if match, then is_latest =1, then fill other fields (transliteration, transliteration_clean,transliteration_for_search) I had used grouping for this to select the latest date in the group. https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
+```sql
+UPDATE cdli_db.inscriptions
+left join cdli_db_old.revhistories on 
+cdli_db_old.revhistories.id = cdli_db.inscriptions.id
+set cdli_db.inscriptions.is_latest = 1
+WHERE cdli_db.inscriptions.id IN (SELECT t1.id
+FROM revhistories t1
+WHERE t1.id = (SELECT t2.id
+                 FROM revhistories t2
+                 WHERE t2.object_id = t1.object_id            
+                 ORDER BY t2.id DESC
+                 LIMIT 1));
+```
+
 
 ## 1.3.2 bring fulltrans data to latest inscription
 
 use the revhistories.id as inscriptions.id
 get update_events_id by using revhistories.mod_date
 
-update the is_latest field and other atf fields   
+
   
 ```sql
 update cdli_db.inscriptions
 join cdli_db_old.fulltrans on cdli_db_old.fulltrans.object_id = cdli_db.inscriptions.artifact_id
-
 set  
-cdli_db.inscriptions.is_latest =1,
 cdli_db.inscriptions.transliteration = cdli_db_old.fulltrans.transliteration
 cdli_db.inscriptions.transliteration_clean = cdli_db_old.fulltrans.transliteration_clean
 cdli_db.inscriptions.transliteration_for_search = cdli_db_old.fulltrans.transliteration_for_search