Purpose

This article will help you generating the necessary queries to move all pin comments from your attachments to the page that the attachment is located. This helps keeping the comments in scenarios where there's a need to disable the Preview plugin.

Solution

• Run the query below against your Confluence database to generate the update queries:

  SELECT DISTINCT concat('UPDATE content SET pageid=',c1.pageid,' WHERE pageid=',c2.pageid, ';')
  FROM content c1
  INNER JOIN content c2 ON c1.contentid = c2.pageid
  WHERE c1.contenttype = 'ATTACHMENT'
    AND c2.contenttype = 'COMMENT';

  Note that depending on your database, the content table name might be in uppercase, so you'll need to update the query to match it.

• The query above will return the queries that you need to run against your database. Here is an example of results:

                            concat                           
  
  -----------------------------------------------------------
  
   UPDATE content SET pageid=26542115 WHERE pageid=26542332;
  
   UPDATE content SET pageid=14221492 WHERE pageid=14221493;
  
   UPDATE content SET pageid=26542123 WHERE pageid=26542125;
  
  (3 rows)

  Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

• After generating the queries and saving a backup copy of your database, shut down your Confluence instance.
• While Confluence is down, please run the queries against the application's database.
• Start Confluence again and check if the comments were migrated successfully.