How to update records in a bulk in customfieldvalue table of jira database in single project

Naren3

Hi,

I have a MultiUserPicker customfield with its id 10902.

At the DB level I want to update some specific users present in this customfield for all issues in a specific set of projects only to different usernames in bulk without affecting those users in other projects for that customfield.

In a CUSTOMFIELDVALUE table of jira database, currently I can update the STRINGVALUE column having a specific user to a different user only for CUSTOMFIELD=10902 only for specific ISSUE.

Ex: UPDATE customfieldvalue

SET stringvalue = 'new_user'

WHERE stringvalue = 'old_user' AND

customfield = 10902 AND

ISSUE = 436023;

I tried using the JIRAISSUE table for referring the column jiraissue.ID and jiraissue.PROJECT in the UPDATE query, but not successful.

How can I update this STRINGVALUE column in bulk for CUSTOMFIELD=10902 for all issues of specific set of projects only.

Thanks!

Nic Brough -Adaptavist-

You do have Jira offline, and you re-indexed it after restarting, right? If not, then you could have done a lot of damage to your issues.

Anyway, the SQL you need to relate Issue to Project is probably along the lines of

select jiraissue.ID from jiraissue join project on jiraissue.project = project.id where project.key in (list of project keys)

(Caveat - I am NOT a DBA, I just know that will answer the question by giving you a list of all the issue IDs for a list of projects. I'm not going to pretend it's elegant, clever, performant, or the best way to do it)