Hello IT Ninjas! Like many people on here I am battling the email loop issue with the K1000 management appliance.  I am not a SQL guy, but we have received a script that is supposed to reset the submitter and CC list to blank if there are more than 15 comments in 15 minutes.  However, when we ran it last night during a loop I got the error shown below. Would be grateful for any help.  

Select SQL:

SELECT HD_TICKET_ID, COUNT(ID) NumberUpdates FROM ORG1.HD_TICKET_CHANGE

WHERE

TIMESTAMP > DATE_SUB(NOW(), INTERVAL 15 MINUTE)

GROUP BY HD_TICKET_ID

HAVING NumberUpdates > 15

ORDER BY COUNT(ID) DESC

 

 

Update SQL:

UPDATE

HD_TICKET

set SUBMITTER_ID = 0,

OWNER_ID = 0,

CC_LIST=''

WHERE ID in ()

 

Error:

03/13/2017 17:38:46> Starting: 03/13/2017 17:38:46 03/13/2017 17:38:46> Executing Select Query... 03/13/2017 17:38:46> selected 1 rows 03/13/2017 17:38:46> Executing Update Query... 03/13/2017 17:38:46> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 6] in EXECUTE("UPDATE HD_TICKET set SUBMITTER_ID = 0, OWNER_ID = 0, CC_LIST='' WHERE ID in () ") 03/13/2017 17:38:46> Ending: 03/13/2017 17:38:46

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2
this may be because you have not defined the array into which the selected tickets from the select statement are entered in your update statement.

rather than "Where ID in ()" try

 "where 
        (HD_TICKET.ID in (<TICKET_IDS>))"
Answered 03/16/2017 by: Hobbsy
Red Belt

  • Made the suggested update and ran the rule this morning while an email loop was happening. Still getting an error. (Shown Below). For some reason it really seems to have an issue with the TICKET_IDS
  • 03/20/2017 08:25:22> Starting: 03/20/2017 08:25:22 03/20/2017 08:25:22> Executing Select Query... 03/20/2017 08:25:22> selected 1 rows 03/20/2017 08:25:22> Executing Update Query... 03/20/2017 08:25:22> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 7] in EXECUTE("UPDATE HD_TICKET set SUBMITTER_ID = 0, OWNER_ID = 0, CC_LIST='' where (HD_TICKET.ID in ()) ") 03/20/2017 08:25:22> Ending: 03/20/2017 08:25:22
Please log in to comment
Answer this question or Comment on this question for clarity