Page Comparison

...

1. Detect templates with duplicated items
   SELECT "ID" as tempalte_id, "IMPORT_TYPE", "NAME" as template_name, "CHECKLIST_ID" as "CHECKLIST_ID" FROM "AO_C2A220_TEMPLATE" acat
WHERE "CHECKLIST_ID" IN (
SELECT sub."CHECKLIST_ID"
FROM ( SELECT "CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values
FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
) AS sub
GROUP BY sub."CHECKLIST_ID"
HAVING COUNT(sub."VALUE") = SUM(sub.count_values) / 2 );

2. Delete item details. The checklist ids can be found in the result of the first query.
   delete from "AO_C2A220_QUOTE" where "ITEM_ID" in (
select min from( SELECT MIN("ID"),"CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values
FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
having COUNT("VALUE") > 1
-- and "CHECKLIST_ID" = 11 -- ADD THIS CONDITION ALSO TO delete checklist by checklist, not all at once ) as abc
) ;
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

3. Delete duplicate itemsitem assignee. The checklist ids can be found in the result of the first query.
   delete from "AO_C2A220_ITEMASSIGNEE" where "ITEM_ID" INin (
select min from(
SELECT MIN("ID"),"CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values
FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
having COUNT("VALUE") > 1
-- and "CHECKLIST_ID" = 11 -- ADD THIS CONDITION ALSO TO delete checklist by checklist, not all at once ) as abc
) ;
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

4. Repeat step 2 and 3 (using "CHECKLIST_ID" = your value) as many times as there were templates found in the first query
   

Oracle

1. Detect templates with duplicated items
   SELECT "ID" AS template_id, "NAME" AS template_name, "CHECKLIST_ID"
FROM "AO_C2A220_TEMPLATE" acat
WHERE "CHECKLIST_ID" IN (
SELECT sub."CHECKLIST_ID"
FROM (
   SELECT

2. Status
   colour Yellow title Optional

   Delete linked items - needed only for Linked templates that are imported to the issues (IMPORT_TYPE - LINKED). The checklist ids can be found in the result of the first query.
   delete from "AO_C2A220_LINKED_ITEM" where "ITEM_ID" in (
select min from( SELECT MIN("ID"),"CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values
FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
) sub
GROUP BY sub.having COUNT("VALUE") > 1
-- and "CHECKLIST_ID"
HAVING COUNT(sub."VALUE") = SUM(sub.count_values) / 2 );Delete item details= 11 -- ADD THIS CONDITION ALSO TO delete checklist by checklist, not all at once ) as abc
) ;
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

3. Delete duplicate items. The checklist ids can be found in the result of the first query.
   DELETE FROM delete from "AO_C2A220_QUOTEITEM"
WHERE where "ITEM_ID" IN(
SELECT select min _id FROM from(
SELECT MIN("ID") AS min_id,"CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values
FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
HAVING having COUNT("VALUE") > 1
-- AND and "CHECKLIST_ID" = 11 -- ADD THIS CONDITION ALSO TO delete checklist by checklist, not all at once ) as abc
);
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first queryDelete duplicate items. The checklist ids can be found in the result of the first query.
   DELETE FROM "AO_C2A220_ITEM"
WHERE "ID" IN (
SELECT min_id FROM (
   SELECT MIN("ID") AS min_id, "CHECKLIST_ID", "VALUE",

4. Repeat steps 2, 3, and 4 (using "CHECKLIST_ID" = your value) as many times as there were templates found in the first query
   

Oracle

1. Detect templates with duplicated items
   SELECT "ID" AS template_id, "IMPORT_TYPE", "NAME" AS template_name, "CHECKLIST_ID"
FROM "AO_C2A220_TEMPLATE" acat
WHERE "CHECKLIST_ID" IN (
SELECT sub."CHECKLIST_ID"
FROM (
SELECT "CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values
FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
HAVING COUNT("VALUE") > 1
-- AND ) sub
GROUP BY sub."CHECKLIST_ID"
HAVING COUNT(sub." = 11 ) );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

2. Repeat step 2 and 3 (using "CHECKLIST_ID" = your value) as many times as there were templates found in the first query
   

MySQL

1. Detect templates with duplicated items
   SELECT `ID` AS template_id, `NAME` AS template_name, `CHECKLIST_ID`
FROM `AO_C2A220_TEMPLATE` acat
WHERE `CHECKLIST_ID` IN (
SELECT sub.`CHECKLIST_ID`
FROM (
SELECT `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values
FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
) sub
GROUP BY sub.`CHECKLIST_ID`
HAVING COUNT(sub.`VALUE`) = SUM(sub.count_values) / 2 );

2. Delete item details. The checklist ids can be found in the result of the first query.
   DELETE FROM `AO_C2A220_QUOTE`
WHERE `ITEM_ID` IN (
SELECT min_id FROM (
SELECT MIN(`ID`) AS min_id, `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
HAVING COUNT(`VALUE`) > 1
-- AND `CHECKLIST_ID` = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

3. Delete duplicate itemsVALUE") = SUM(sub.count_values) / 2 );

4. Delete item details. The checklist ids can be found in the result of the first query.
   DELETE FROM "AO_C2A220_QUOTE"
WHERE "ITEM_ID" IN (
SELECT min_id FROM (
SELECT MIN("ID") AS min_id, "CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
HAVING COUNT("VALUE") > 1
-- AND "CHECKLIST_ID" = 11 ) );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

5. Delete item assignee. The checklist ids can be found in the result of the first query.
   DELETE FROM "AO_C2A220_ASSIGNEE"
WHERE "ITEM_ID" IN (
SELECT min_id FROM (
SELECT MIN("ID") AS min_id, "CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
HAVING COUNT("VALUE") > 1
-- AND "CHECKLIST_ID" = 11 ) );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

6. Status
   colour Yellow title Optional

   Delete linked items - needed only for Linked templates that are imported to the issues (IMPORT_TYPE - LINKED). The checklist ids can be found in the result of the first query.
   DELETE FROM "AO_C2A220_LINKED_ITEM"
WHERE "ITEM_ID" IN (
SELECT min_id FROM (
SELECT MIN("ID") AS min_id, "CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
HAVING COUNT("VALUE") > 1
-- AND "CHECKLIST_ID" = 11 ) );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

7. Delete duplicate items. The checklist ids can be found in the result of the first query.
   DELETE FROM "AO_C2A220_ITEM"
WHERE "ID" IN (
SELECT min_id FROM (
SELECT MIN("ID") AS min_id, "CHECKLIST_ID", "VALUE", COUNT("VALUE") AS count_values FROM "AO_C2A220_ITEM" acai
GROUP BY "CHECKLIST_ID", "VALUE"
HAVING COUNT("VALUE") > 1
-- AND "CHECKLIST_ID" = 11 ) );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

8. Repeat steps 2, 3, and 4 (using "CHECKLIST_ID" = your value) as many times as there were templates found in the first query
   

MySQL

1. Detect templates with duplicated items
   SELECT `ID` AS template_id, `IMPORT_TYPE`, `NAME` AS template_name, `CHECKLIST_ID`
FROM `AO_C2A220_TEMPLATE` acat
WHERE `CHECKLIST_ID` IN (
SELECT sub.`CHECKLIST_ID`
FROM (
SELECT `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values
FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
) sub
GROUP BY sub.`CHECKLIST_ID`
HAVING COUNT(sub.`VALUE`) = SUM(sub.count_values) / 2 );

2. Delete item details. The checklist ids can be found in the result of the first query.
   DELETE FROM `AO_C2A220_QUOTE`
WHERE `ITEM_ID` IN (
SELECT min_id FROM (
SELECT MIN(`ID`) AS min_id, `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
HAVING COUNT(`VALUE`) > 1
-- AND `CHECKLIST_ID` = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

3. Delete item assignee. The checklist ids can be found in the result of the first query.
   DELETE FROM `AO_C2A220_ASSIGNEE`
WHERE `ITEM_ID` IN (
SELECT min_id FROM (
SELECT MIN(`ID`) AS min_id, `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
HAVING COUNT(`VALUE`) > 1
-- AND `CHECKLIST_ID` = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

4. Status
   colour Yellow title Optional

   Delete linked items - needed only for Linked templates that are imported to the issues (IMPORT_TYPE - LINKED). The checklist ids can be found in the result of the first query.
   DELETE FROM `AO_C2A220_LINKED_ITEM`
WHERE `ITEM_ID` IN (
SELECT min_id FROM (
SELECT MIN(`ID`) AS min_id, `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
HAVING COUNT(`VALUE`) > 1
-- AND `CHECKLIST_ID` = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

5. Delete duplicate items. The checklist ids can be found in the result of the first query.
   DELETE FROM `AO_C2A220_ITEM`
WHERE `ID` IN (
SELECT min_id FROM ( SELECT MIN(`ID`) AS min_id, `CHECKLIST_ID`, `VALUE`, COUNT(`VALUE`) AS count_values
FROM `AO_C2A220_ITEM` acai
GROUP BY `CHECKLIST_ID`, `VALUE`
HAVING COUNT(`VALUE`) > 1
-- AND `CHECKLIST_ID` = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

6. Repeat steps 2, 3, and 4 (using "CHECKLIST_ID" = your value) as many times as there were templates found in the first query

Microsoft SQL

1. Detect templates with duplicated items
   SELECT [ID] AS template_id, [IMPORT_TYPE], [NAME] AS template_name, [CHECKLIST_ID]
FROM [AO_C2A220_TEMPLATE] acat
WHERE [CHECKLIST_ID] IN (
SELECT sub.[CHECKLIST_ID]
FROM (
SELECT [CHECKLIST_ID], [VALUE], COUNT([VALUE]) AS count_values
FROM [AO_C2A220_ITEM] acai
GROUP BY [CHECKLIST_ID], [VALUE]
) sub
GROUP BY sub.[CHECKLIST_ID]
HAVING COUNT(sub.[VALUE]) = SUM(sub.count_values) / 2 );

2. Delete item details. The checklist ids can be found in the result of the first query.
   DELETE FROM `AO[AO_C2A220_ITEM` QUOTE]
WHERE `ID` [ITEM_ID] IN (
SELECT min_id FROM (
SELECT MIN(`ID`[ID]) AS min_id, `CHECKLIST[CHECKLIST_ID`, `VALUE`ID], [VALUE], COUNT(`VALUE`[VALUE]) AS count_values
FROM `AO[AO_C2A220_ITEM` ITEM] acai
GROUP BY `CHECKLIST[CHECKLIST_ID`, `VALUE` ID], [VALUE]
HAVING COUNT(`VALUE`[VALUE]) > 1
-- AND `CHECKLIST[CHECKLIST_ID` ID] = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

3. Repeat step 2 and 3 (using "CHECKLIST_ID" = your value) as many times as there were templates found in Delete item assignee. The checklist ids can be found in the result of the first query

Microsoft SQL

1. Detect templates with duplicated items
   SELECT [ID] AS template_id, [NAME] AS template_name, [CHECKLIST_ID]
   .
   DELETE FROM [AO_C2A220_TEMPLATEASSIGNEE] acat
WHERE [CHECKLISTITEM_ID] IN (
SELECT min_id FROM (
SELECT sub.MIN([CHECKLIST_ID]
FROM (
SELECT ) AS min_id, [CHECKLIST_ID], [VALUE], COUNT([VALUE]) AS count_values
FROM [AO_C2A220_ITEM] acai
GROUP BY [CHECKLIST_ID], [VALUE]
) sub
GROUP BY sub.[CHECKLIST_ID]
HAVING COUNT(sub.[VALUE]) = SUM(sub.count_values) / 2 );Delete item detailsHAVING COUNT([VALUE]) > 1
-- AND [CHECKLIST_ID] = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

2. Status
   colour Yellow title Optional

   Delete linked items - needed only for Linked templates that are imported to the issues (IMPORT_TYPE - LINKED). The checklist ids can be found in the result of the first query.
   DELETE FROM [AO_C2A220_LINKED_QUOTEITEM]
WHERE [ITEM_ID] IN (
SELECT min_id FROM (
SELECT MIN([ID]) AS min_id, [CHECKLIST_ID], [VALUE], COUNT([VALUE]) AS count_values FROM [AO_C2A220_ITEM] acai
GROUP BY [CHECKLIST_ID], [VALUE]
HAVING COUNT([VALUE]) > 1
-- AND [CHECKLIST_ID] = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

3. Delete duplicate items. The checklist ids can be found in the result of the first query.
   DELETE FROM [AO_C2A220_ITEM]
WHERE [ID] IN (
SELECT min_id FROM (
SELECT MIN([ID]) AS min_id, [CHECKLIST_ID], [VALUE], COUNT([VALUE]) AS count_values FROM [AO_C2A220_ITEM] acai
GROUP BY [CHECKLIST_ID], [VALUE]
HAVING COUNT([VALUE]) > 1
-- AND [CHECKLIST_ID] = 11 ) AS subquery );
   where for "CHECKLIST_ID" = 11 replace 11 with the value from the first query

4. Repeat step steps 2, 3, and 3 4 (using "CHECKLIST_ID" = your value) as many times as there were templates found in the first query

...