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

[Naren]

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- · Accepted solution]

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)

[Naren]

Yeah I handle it offline.

[JamieA]

You need to join jiraissue to project - what query do you have so far? What DBMS?

[Naren]

I use Oracle DBMS.

Following is the query I am trying to get it working

update  customfieldvalue cfv 
set     cfv.stringvalue = 'devinda' 
where   cfv.stringvalue='devindam' and  
        cfv.customfield=10901 and cfv.issue 
IN     (select distinct ji.id 
        from jiraissue ji,  customfieldvalue cfv  
        where ji.project= 19200 and 
        ji. id = cfv.issue and 
        cfv.stringvalue='devindam' and 
       cfv.customfield=10901)

Kindly let me know if the correct query

Thanks!

[JamieA]

In the inner query, you need to join on project, and not on cfv.