Created this SQL-statement used for one of our JIRA-instances. Needed to get rid of a lot of open sub-tasks where the parent task had been cancelled. The statement also shows usage of inner join and self-join.
U PDATE jiraissue ji1, jiraissue ji2
INNER JOIN issuelink il
SET ji1.resolution = 9, ji1.issuestatus = 6
WHERE ji1.resolution IS NULL
AND ji1.id = il.destination
AND ji1.issuestatus = 1
AND il.source = ji2.id
AND ji2.resolution = 9;
