nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Uday Kiran Raparthy

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.

Accepted answers

Nic Brough -Adaptavist-

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.

All comments

Joshua Sneed Contegix

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!

Nic Brough -Adaptavist-

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.

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

@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-

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)

Joshua Sneed Contegix

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server

database