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.
- 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