Bulk fix publication dates for archives

[czirfusz]

Hi,

we have a large amount of archives with incorrect submission publication dates. The publication dates are either missing or showing the date of upload onto the web.

The archives were produced partly with XML import in OJS2 and partly with the QuickSubmit plugin in OJS2. Now we have a running OJS3.1.1.1 install.

What I wish is to populate and overwrite the publication dates of the submissions for all published articles with the issue publication dates (the latter are correct for the whole archives). I have access to the database via phpmyadmin but do not know SQL to write a script like that.

Thank you for any suggestions.

[GrazingScientist]

Although this is a very old issue, I would like to share my SQL code that saved me a lot of time.

UPDATE published_submissions
INNER JOIN issues ON
published_submissions.issue_id = issues.issue_id
SET
published_submissions.date_published = issues.date_published;

I strongly recommaned to make a database backup before and perhaps test it in an isolated database. This SQL was tested with OJS 3.1.0.1 .

Cheers,

Adrian

[Ramfra]

Hi,

We had the same problem with a large number of archive articles that had been uploaded using QuickSubmit.

We are running OJS 3.3.0.8, and apparently the SQL that is shared above no longer works since the database schema has since changed quite a bit.

In case it can help others who come across the same issue in the future, here is the code we successfully used:

UPDATE publications JOIN ( SELECT issues.date_published AS date, publications.publication_id AS id FROM journal_settings INNER JOIN sections on journal_settings.journal_id = sections.journal_id INNER JOIN publications on sections.section_id = publications.section_id INNER JOIN publication_settings on publications.publication_id = publication_settings.publication_id INNER JOIN issues on publication_settings.setting_value = issue_id WHERE journal_settings.setting_name = 'onlineIssn' AND journal_settings.setting_value = 'xxxx-xxxx' AND publication_settings.setting_name = 'issueId' ) sq ON sq.id = publications.publication_id SET date_published = sq.date;