Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

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.

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

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

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

Like Nic Brough _Adaptavist_ likes this

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
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you