Custom Ticket Rule - Add address to CC_LIST through Update Query
I don't like using the Category CC_LIST so have a custom ticket rule set up to include specific addresses to the ticket CC_LIST when a specific category is selected on a new ticket. My update script is not working though. I've included the Update SQL below to show what I've done. I did swapp out the emails I used for a generic one just for this post. The addresses I am really using are valid though. I've tried with one or multiple email addresses and it will not update the CC_LIST. Any and all help is greatly appreciated.
update HD_TICKET , HD_CATEGORY
set HD_TICKET.CC_LIST as 'generic@email.com, generic2@email.com'
where HD_CATEGORY.ID = '150' and HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
When i run the rule I get the following error in the run log:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'generic@email.com, generic2@email.com'
where CAT.ID = '150' and HD_TICKET.HD_CATEGORY_ID = CAT.I' at line 2] in EXECUTE("update HD_TICKET , HD_CATEGORY AS CAT
set HD_TICKET.CC_LIST as 'generic@email.com, generic2@email.com'
where CAT.ID = '150' and HD_TICKET.HD_CATEGORY_ID = CAT.ID")
Answers (2)
update HD_TICKET T set T.CC_LIST = 'generic@email.com, generic2@email.com' WHERE T.ID IN () This is how I ended up setting up the Update query leaving the requirements to the select query
What happens when you change "set HD_TICKET.CC_LIST as" to "set HD_TICKET.CC_LIST ="
Comments:
-
I changed it to an = sign but it is still not updating the ticket. Though I don't get an error now. it says that it updated 5 rows though this ticket is not updated with any addresses in the CC_LIST. Any other suggestions? - sdnbtech1 11 years ago
-
Thanks dugullett, got me on my way to figuring it out a little more - sdnbtech1 11 years ago
Your rule doesn't include a statement to indicate which ticket to update. Normally it should include something like WHERE HD_TICKET.ID in <ticket_ids>
Comments:
-
Thanks Chucksteel, I kind of figured that out when I tested it and changed the CC_List on 500+ tickets. :-( Lesson learned lol - sdnbtech1 11 years ago
-
Do I have to set the <ticket_ids> variable or does KACE assign that to the ticket id's that come from the select query in the rule - sdnbtech1 11 years ago
-
KACE populates it based on the tickets that match the select query. - chucksteel 11 years ago
-
Good to know, thanks man. - sdnbtech1 11 years ago