Changing server ID in JIRA

[DanielG]

Hi,

I follow this documentation:

https://confluence.atlassian.com/display/JIRAKB/Changing+Server+ID+for+Test+Installations

I put this query on my database in JIRA, with ID that I want:

UPDATE propertystring SET propertyvalue = '<ID>' where id = (select id from propertystring where id in (select id from propertyentry where PROPERTY_KEY='jira.sid.key'));

But return this error:

ERROR 1093 (HY000): You can't specify target table 'propertystring' for update in FROM clause

Can anybody help me ?

I can't change my server ID of my JIRA :(

Thanks!

[DanielG · Accepted solution]

Finally, this was the sentence SQL that I used to change my Server ID on JIRA:

UPDATE propertystring SET propertyvalue = 'XXXX-XXXX-XXXX-XXXX' where id= XXXX


You have to search which ID you have to change with query:

select * from propertystring where id in (select id from propertyentry where PROPERTY_KEY='jira.sid.key');

[Mikhail T]

MySQL does not allow modifying tables, that are mentioned in subqueries of the UPDATE. So rephrase your update to avoid subqueries. Something like (untested):

UPDATE propertystring, propertyentry SET propertyvalue = 'XXXX-XXXX-XXXX-XXXX' WHERE propertstring.id=propertyentry.id AND propertyentry.PROPERTY_KEY='jira.sid.key'

Test the query with SELECT instead of UPDATE. Yes, you can list multiple tables in UPDATE even if you are only modifying some of them...

[Timothy]

Your SQL query must be wrong and may need the extra schemas/domain included with the table name. Fix it.

[DanielG]

I test:

select * from propertystring where id in (select id from propertyentry where PROPERTY_KEY='jira.sid.key');

And works, return:

ID | property value
10006 | XXXX-XXXX-XXXX-XXXX

I test this:

UPDATE propertystring SET propertyvalue = 'XXXX-XXXX-XXXX-XXXX' where id in (select id from propertystring where id in (select id from propertyentry where PROPERTY_KEY='jira.sid.key'));


And not works... I get the same error... why??

[Robert Mota]

Hi,

Why to put in addition "select id from propertystring where id..."

Is it not simpliest as below ?

UPDATE propertystring SET propertyvalue = 'XXXX-XXXX-XXXX-XXXX' 
where id in (
    select id from propertyentry 
    where PROPERTY_KEY='jira.sid.key'
);

Best regards

[Bhushan Nagaraj]

UPDATE propertystring SET propertyvalue = 'XXXX-XXXX-XXXX-XXXX' where id in (select id from propertystring where id in (select id from propertyentry where PROPERTY_KEY='jira.sid.key'));

[DanielG]

Don't work for me. I still get the same error...

[Zul NS _Atlassian_]

Need someone who is good in MSSQL on this. Quick search lead me to this and this link.