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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2
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:


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 line


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.
Answered 12/21/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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
Answered 12/21/2011 by: Thatcher.Deane
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity