Wondering how to bulk disable options for a custom field in Jira? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team recently helped a customer with a Jira query.

How to bulk disable options for a custom field in Jira?

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.

Please ensure the values you want to show are returned with the following query:

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 are happy this query returns everything you want to change, you can proceed with the solution.

Today, let us see the steps followed by our Support Techs to resolve it.

Always back up your data before performing any modifications to the database.

If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

• Firstly, shut down Jira, and perform a full database backup
• Then, execute the following SQL:

UPDATE 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:

1093: You can’t specify target table ‘customfieldoption’ for update in FROM clause

This query should work instead:

UPDATE 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.

[Looking for another solution to this query? We are just a click away.]

Conclusion

To conclude, the skilled Support Engineers at Bobcares demonstrated how to bulk disable options for a custom field in Jira.