Monday, November 11, 2013

updating remote database with records in one field

We had to update the 'firstplayed' field of the schedule page database on our website. Our local database had the info. The way to do this seemed to be:

  • Export the filename/firstplayed fields from remote db as csv, 
  • import into local db as a table, create a view (run a join query) to get the desired firstplayed field for each filename, 
  • export as csv, 
  • import into remote db, 
  • run an update query there. 
But Murphy's law was evident at every step. My database guru roommate Nz helped out to navigate the Murphy minefield. Remote db was MySql, with phpMyAdmin front-end. Local db was Postgres, with phpPgAdmin front end. The import button comes up in phpPgAdmin while viewing a particular table.

  • phpMyAdmin's default csv output had ; (semicolon) as default separator, phpPgAdmin's import needed , (comma) as separator. 
  • The remote db often took a long time to respond since it is on shared hosting. 131 seconds to update 2000 records, for example.
  • phpPgAdmin demanded the field names as the first row of the csv
  • phpMyAdmin ignored the field names as the first row, took that row as data to be entered, and mixed up the fields - firstplayed was entered as filename and vice versa!
  • Since there were many streams, the firstplayed from the local db had multiple matches, quite a few with NULL. Just ignored them, and used a NOT NULL condition. Syntax for postgres - IS NOT NULL - not not equal NULL. :)
  • Had to do a left() firstplayed field, since the local db had a full timestamp, while remote db needed only the date. 
  • Telugustream has a lot of files yet to be updated in the temp_play_history table. So will have to do the above procedure again.
  • Finally, the update query syntax for mysql was non-standard - Access style, as my db guru roommate mentioned - instead of update dt set df=sf from dt,st where d.jf=s.jf, the syntax was
    update `dt`,`st` set `dt`.`df`  = left(`st`.`sf`,10) where `dt`.`df` = `st`.`sf`

No comments:

Post a Comment