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:

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

• Shut down Jira, and perform a full database backup

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