Deleting unused categories with ticket rule
Hi. In a test environment we want to delete (most) categories for a particular queue. All tickets in the queue have been reassigned (via ticket rule) to a '----HoldingCategory'
This would appear to be the SQL to remove the other, 'empty' categories.
DELETE FROM HD_CATEGORY WHERE HD_QUEUE_ID=4 AND NAME <> '----HoldingCategory'
Do I just drop this into the update window for a ticket rule in this queue, (with a SELECT 1 in the select window) and then run ... as in the very helpful 'How To Import HelpDesk Categories from a Spreadsheet' http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=778&artlang=en
[/align]
Thanks!
-T
This would appear to be the SQL to remove the other, 'empty' categories.
DELETE FROM HD_CATEGORY WHERE HD_QUEUE_ID=4 AND NAME <> '----HoldingCategory'
Do I just drop this into the update window for a ticket rule in this queue, (with a SELECT 1 in the select window) and then run ... as in the very helpful 'How To Import HelpDesk Categories from a Spreadsheet' http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=778&artlang=en
[/align]
Thanks!
-T
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
GillySpy
12 years ago
The kbox may no like you very much if you delete categories that are assigned to tickets so I would write the delete (update query) this way:
Any categories that you want to delete that do have tickets tied to them you can use the rule wizard to change their category to something else then re-run that delete.
But yes, if you put "select 1" in the select query then the update will fire no matter what.
DELETE CAT from HD_CATEGORY CAT LEFT JOIN HD_TICKET T ON CAT.ID=T.HD_CATEGORY_ID
WHERE T.ID IS NULL and CAT.HD_QUEUE_ID=4
and CAT.NAME IN ('cat1','cat2','cat3') -- to delete all unassigned categories remove this entire lineAny categories that you want to delete that do have tickets tied to them you can use the rule wizard to change their category to something else then re-run that delete.
But yes, if you put "select 1" in the select query then the update will fire no matter what.
Posted by:
Thatcher.Deane
12 years ago
Hi Gerald,
Thanks for the more surgical delete, but in this *test* environment we have over 200 categories to delete so ... being entirely certain that no tickets were assigned to any category except the one we were not deleting ... used the more risky SQL statement below and all seems fine. Will be sure not to this way in a production environment.
Now I'm wondering if I could have used a ticket rule (or SQL) to set category unassigned and then used your safer SQL statement above, without the last line. Duh.
Thanks.
-T
Thanks for the more surgical delete, but in this *test* environment we have over 200 categories to delete so ... being entirely certain that no tickets were assigned to any category except the one we were not deleting ... used the more risky SQL statement below and all seems fine. Will be sure not to this way in a production environment.
Now I'm wondering if I could have used a ticket rule (or SQL) to set category unassigned and then used your safer SQL statement above, without the last line. Duh.
Thanks.
-T
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.