How to write a SQL query to update custom field data to jira “description” field?

Ganga_Selvarajah May 13, 2012

Hi,

How to write a SQL query to update custom field data to jira “description” field?

The custom field name = Issue Description
Filed Type = Free Text Field unlimited text

How do I update from above mention custom field to jira “description” field for a "selected Project" by using with SQL query.?

Could you please provide the SQL script, so that I can use that query to update the data from custom field to description field.

Thanks
Ganga S

3 answers

1 accepted

0 votes
Answer accepted
Dieter
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.
May 14, 2012

Hi Ganga,

The following works on MySQL but i'm not sure if other databases understand that as well

update jiraissue,customfieldvalue
set jiraissue.description = customfieldvalue.textvalue 
where customfield = 12345 and jiraissue.id = customfieldvalue.issue and jiraissue.project = xxxxx;

12345 is the id of your unlimited text area custom field, xxxxx is the id of your project

Please always follow Nics hints for such operations

Ganga_Selvarajah May 15, 2012

Hi Dieter,

Thanks for the SQL query. It is really useful.

I am using MS SQL 2008 R2, first I will try on our test environment and let you know the status.

Sure, I will follow the @Nic instructions before proceed and also I took the table backup using with

Select *into jiraschema.jiraissue_bak fromjiraschema.jiraissue

It means, for a test, I am going to run the update query on the backup table instead actual table

Ganga S

Ganga_Selvarajah May 15, 2012

Hi Nic/Dieter.

Firstly: Thanks for the help....

It works. I used outer join query to update data from custom field to jira issue description field on a SQL 2008 R2 database. Outer join updates only matching records.

update jiraschema.jiraissue
set jiraschema.jiraissue.description = jiraschema.customfieldvalue.textvalue
from jiraschema.jiraissue left join jiraschema.customfieldvalue
on jiraschema.customfieldvalue.issue = jiraschema.jiraissue.id
where jiraschema.customfieldvalue.customfield = 12345
and jiraschema.jiraissue.project = xxxxx;

Ganga S

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 14, 2012

It's not a simple script. To get the data out, you need to read "jiraissue where project = xxxxx" (xxxxx being the project you're interested in - read table "project" to find that), then for each jiraissue record found, read "customfieldvalue.text where issue = jiraissue.id and customfield = yyyyy" (where yyyyy = the custom field id for your free text field). Finally, to write the data, you update jiraissue.description with the value you've just read.

Please remember that when you do this, you absolutely MUST

  1. Take Jira offline
  2. Take a backup
  3. Restore it to prove it is a good backup
  4. Run the SQL
  5. Restart Jira
  6. Re-index it

Ganga_Selvarajah May 14, 2012

Thanks Nic,

I am nerves with complex query, but I will try on my test machine….

Ganga S

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2012

Always a good idea to test first :-)

The SQL Dieter gives looks correct to me, with a couple of minor points:

  1. I think it will overwrite all the descriptions on all the issues with the value of the custom field, which may not be what you want.
  2. Also, I'm not sure how MS-SQL will react if the customfield is empty. When a field is empty, Jira doesn't have any record at all for the value of the field, so "customfieldvalue.textvalue" won't come back with anything.
Ganga_Selvarajah May 15, 2012

Thanks for the second consideration....

#1. I wanted to overwrite all the issues description for a particular "Project". It means not for other project issues description.

#2. It is an interesting point.... I will try on test machine first..... and update the status.

Ganga S

0 votes
Venkata.Potli1 April 29, 2021

Hi @Nic Brough -Adaptavist- Could you please help me How to write a SQL query to update custom field data to jira “due date” field?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 29, 2021

due date is not a custom field.

I would very strongly recommend not using SQL to change data in Jira.  To do it, you need Jira backed up, shut down and fully re-indexed after restart, so it's slow, clunky and inefficient.

What are you really trying to achieve?

Suggest an answer

Log in or Sign up to answer