I'm really new to SQL queries and I'm trying to write a custom ticket rule to move tickets from the default queue to another queue based on the default queue of the submitter.  I think my select statement is ok and my update query makes sense to me but I'm getting a duplicate primary key error.  Here is what I have.

Select SQL: 

 

SELECT ORG1.HD_TICKET.id

            FROM ORG1.USER INNER JOIN ORG1.HD_TICKET

                        ON ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID

        WHERE ORG1.HD_TICKET.id = '10'


Run update query 

Update SQL:

 

UPDATE ORG1.HD_TICKET JOIN ORG1.USER

            ON  ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID

           

    SET ORG1.HD_TICKET.id = ORG1.USER.HD_DEFAULT_QUEUE_ID   Does this look like I'm in the ballpark?  I'm not sure where to go from here.

 
The error I'm getting is this:

05/08/2015 09:17:19> Starting: 05/08/2015 09:17:19 05/08/2015 09:17:19> Executing Select Query... 05/08/2015 09:17:19> selected 6 rows 05/08/2015 09:17:19> Executing Update Query... 05/08/2015 09:17:19> mysql error: [1062: Duplicate entry '14' for key 'PRIMARY'] in EXECUTE("UPDATE ORG1.HD_TICKET JOIN ORG1.USER ON ORG1.USER.ID = ORG1.HD_TICKET.SUBMITTER_ID SET ORG1.HD_TICKET.id = ORG1.USER.HD_DEFAULT_QUEUE_ID ") 05/08/2015 09:17:19> Recalculating SLA Due Dates... 05/08/2015 09:17:19> updated 6 tickets 05/08/2015 09:17:19> Ending: 05/08/2015 09:17:19

Does this look like I'm in the ballpark?  I'm not sure where to go from here.

Thanks,
     Jonathan
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Your update query is trying to change the ticket ID (HD_TICKET.ID). You want to change the queue ID which is HD_TICKET.HD_QUEUE_ID. 

I would write the rule like this:
UPDATE HD_TICKET
SET HD_TICKET.HD_QUEUE_ID = (SELECT HD_DEFAULT_QUEUE_ID FROM USER WHERE USER.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.ID in <TICKET_IDS>
When you create a rule KACE populates a variable named <TICKET_IDS>. This gets populated with a list of ID values returned from the select statement.


Answered 05/11/2015 by: chucksteel
Red Belt

  • pls check my statement if its correct.
    what im doing is if CUSTOM_FIELD_VALUE8 like '%America%' then move the tickets to queue_ID =20.

    SELECT HD_TICKET.ID, HD_TICKET.TITLE,
    HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
    HD_CATEGORY.ID, HD_CATEGORY.NAME
    FROM HD_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    WHERE HD_TICKET.CUSTOM_FIELD_VALUE8 like '%America%'
    AND HD_TICKET.HD_QUEUE_ID = 16
    ORDER BY HD_TICKET.ID




    UPDATE HD_TICKET
    SET HD_TICKET.HD_QUEUE_ID = 20
    WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
    AND HD_TICKET.ID = <TICKET_IDS>;


    05/11/2016 03:01:34> Starting: 05/11/2016 03:01:34 05/11/2016 03:01:34> Executing Select Query... 05/11/2016 03:01:34> selected 2 rows 05/11/2016 03:01:34> Executing Update Query... 05/11/2016 03:01:34> 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 '122' at line 4] in EXECUTE("UPDATE HD_TICKET SET HD_TICKET.HD_QUEUE_ID = 20 WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND HD_TICKET.ID = 122,122;") 05/11/2016 03:01:34> Ending: 05/11/2016 03:01:34
    • Your select statement is returning multiple ticket IDs so your update statement ends with HD_TICKET.ID = 122,122. You should use HD_TICKET.ID in (<TICKET_IDS>) instead.
      • executes without any error but doesnt move the tickets to other queue



        SELECT HD_TICKET.ID, HD_TICKET.TITLE,
        HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
        HD_CATEGORY.ID, HD_CATEGORY.NAME
        FROM HD_TICKET
        JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        WHERE HD_TICKET.CUSTOM_FIELD_VALUE8 like '%Amer%'
        AND HD_TICKET.HD_QUEUE_ID = 16
        ORDER BY HD_TICKET.ID







        UPDATE HD_TICKET, HD_CATEGORY
        SET HD_TICKET.HD_QUEUE_ID = 22
        WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
        AND HD_TICKET.ID in (<TICKET_IDS>);



        05/12/2016 01:18:57> Starting: 05/12/2016 01:18:57 05/12/2016 01:18:57> Executing Select Query... 05/12/2016 01:18:57> selected 2 rows 05/12/2016 01:18:57> Executing Update Query... 05/12/2016 01:18:57> updated 0 rows 05/12/2016 01:18:57> Ending: 05/12/2016 01:18:57
      • Have you tried just changing the queue and not including the category? So just
        UPDATE HD_TICKET
        SET HD_QUEUE_ID = 22
        WHERE ID in (<TICKET_IDS>)

        Keep in mind that category ID values are unique per queue, so you can't use a category ID from queue 16 for a ticket in queue 22.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share