Insert/update new value to Custom Field JIRA (SQL)

Uday Kiran Raparthy April 14, 2021

I would like to INSERT new values to custom field in JIRA DB, but when there is no value, I can not use UPDATE, because row doesn't exist.

Custom field type is "Select List (single choice)"

UPDATE jiraissue,customfieldvalue
SET customfieldvalue.datevalue = jiraissue.created
WHERE customfieldvalue.customfield = 'XXXXX' AND jiraissue.id = customfieldvalue.issue;

My goal is to INSERT new values to a custom filed with id 'XXXXX' for an Issue that has no value there.

correct me if I am missing anything here.

2 answers

1 accepted

1 vote
Answer accepted
Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 14, 2021

Stop, stop, stop!  You should not even be looking at the database, it is almost always the totally wrong thing to do.

There are times when it's ok, but they are fixing data damaged by bugs, mucking with migrations and when Atlassian support tell you to.

Yes, you can update values in custom fields by running SQL, but in your question, you make no reference to what you would really need to do, so I suspect you are not doing everything that would be necessary.

To add a value to an empty field, you will need to:

  • Block access to Jira
  • Back up your Jira 
  • Prove you can restore from the backup
  • Stop Jira
  • Run SQL to find the sequence numbers you need
  • Run SQL to update the sequences
  • Run SQL to insert the data you want
  • Restart Jira
  • Run a full locking re-index
  • Run tests to make sure your changes have taken and not broken anything
  • Let your people back into it

In other words, don't look at the database.  Use the REST API to make your updates, not SQL.

Uday Kiran Raparthy April 14, 2021

@Nic Brough -Adaptavist- thanks for responding

The SQL script is expected to update all the user stories related in scope to update the customfield1234= 'xxxx' when the value is empty.

Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 14, 2021

I think you may have missed the point of this, so I'll be quite blunt.

Stop using SQL. 

It won't work unless you do what I said in my last post.  And it needs you to completely understand the way Jira uses a database as a data-store (which I would guess that you do not, as you have not mentioned or questioned the sequencing stuff, the caching or how custom fields are stored)

Like # people like this
Joshua Sneed Contegix
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 16, 2021

Seconded.
Stop using SQL.
Use API (java/rest)
Stop with this silliness.

Like Nic Brough -Adaptavist- likes this
Parker_Odom
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
July 11, 2024

So I post a question somewhat similar that I'm hoping you guys might have an answer to. I also am wanting to directly modify the database data BUT that is only because I cannot find any possible solution by using the API. I want to be able to update contexts for custom fields and also what screens are associated with those custom fields without ever touching the GUI. From my searching, Atlassian has deemed that functionality not useful and declined to ever add it to the API. I disagree with their decision while heatedly, but here we are. So in this case unless I have missed something is there a good way to do this by directly modifying this data in the database?

0 votes
Joshua Sneed Contegix
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 14, 2021

Hi Uday,

You are missing the fact that this will cause problems elsewhere. All modifications to the DB should go through the app because there are secondary process that occur. I would shift methods from the database to the API for a path that avoids many pitfalls. Cheers!

Suggest an answer

Log in or Sign up to answer