How to update the JIRA database by conditional Screening

[xinan liu]

I have encountered such a problem, a large number of issues' ResolutionDate isn't correct, they were imported from CSV, the CSV file was wrong, it was 8 hours in advance of real time, which leading to some issues' resolutiondate earlier than the createddate.
Now I want to fix them by updating the database.
These issues dispersed in 60 projects, and these projects have already been used. But fortunately I have a custom field named BugID, all imported issues' BugID contain the phrase "BugFree ", so I can screen all import Issues by BugID.
The problem is, I found ResolutionDate in jira_schema.jiraissue table, and custom fields BugID is in jira_schema.customfieldvalue table, their relationship is linked by ISSUE this column. How can I update the ResolutionDate by screening the table jira_schema.customfieldvalue?

How to write the SQL statement?

I am currently using this SQL statement to deal with this problem, but it can only solve part of the issues' problem.

update [jira].[jira_schema].[jiraissue]

set RESOLUTIONDATE = DateAdd(HOUR, 8, RESOLUTIONDATE)

FROM [jira].[jira_schema].[jiraissue]

where RESOLUTIONDATE < CREATED

My OS is Windows Server 2008 R2 and my SQL Server is Microsoft SQL Server 2008.
Thanks very much.

[Daniel Wester]

The better approach would be to use the rest api to update the issues: https://docs.atlassian.com/jira/REST/latest/#d2e2937

If you interact with the database directly - you'll need to restart JIRA afterwards (I would suggest having a database backup ahead of time) as well as doing a reindex of the instance.