How to bulk disable options for a custom field
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
When working with custom fields that have options (such as the Select List types of fields), you may wish to disable multiple options in bulk to prevent their use - rather than deleting them.
Diagnosis
Ensure the values you want to show are returned with the following query:
1
2
3
4
5
6
SELECT cfo.id, cfo.sequence, cfo.customvalue
FROM customfieldoption cfo
JOIN customfield cf ON cfo.customfield = cf.id
WHERE cf.cfname = '<your field name here>'
AND cfo.disabled = 'N'
ORDER BY cfo.sequence ASCℹ️You can also change the WHERE condition to check for Custom Field IDs. You can also use an IN clause to select the options for multiple custom fields.
Once you're satisfied this query returns everything you want to change (and nothing that you do not want to change), you can proceed with the solution.
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Shut down Jira, and perform a full database backup
Execute the following SQL:
1 2 3 4 5 6 7UPDATE customfieldoption SET disabled = 'Y' WHERE id IN ( SELECT cfo.id FROM customfieldoption cfo JOIN customfield cf ON cfo.customfield = cf.id WHERE cf.cfname = '<your field name here>' AND cfo.disabled = 'N' );⚠️ If you're using MySQL, you might see the following error:
11093: You can't specify target table 'customfieldoption' for update in FROM clauseThis query should work instead:
1 2 3 4 5 6 7 8 9UPDATE customfieldoption SET disabled = 'Y' WHERE id IN ( SELECT myid FROM ( SELECT cfo.id as myid FROM customfieldoption cfo JOIN customfield cf ON cfo.customfield = cf.id WHERE cf.cfname = '<your field name here>' AND cfo.disabled = 'N' ) as t );Restart Jira for the changes to take effect.
Was this helpful?