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