/build/static/layout/Breadcrumb_cap_w.png

My Update SQL has an issue...

I just created a Select, Email & Update SQL Custom Ticker Rule...


The result said that 1 ROW was selected, the E-mail was sent out, but then my Update SQL updated ALL of my ticket in ALL of my Queues..


What I wanted to have happen, was after the E-mail sent out the $comment from the ticket, I then wanted to change the category, erase the comment and change it to 'Info is for Payroll, and has been removed',  & close the ticket all of which I got working...

but I then noticed that the info that was in the ticket "summary/Comment?" was still in the comment section and the history section...


So, I then needed to overwrite those sections as well..

I detected them in the database as HD_TICKET_CHANGE.COMMENT & HD_TICKET_CHANGE.COMMENT_LOC, so I added to my SQL Update

HD_TICKET_CHANGE.COMMENT = 'Info is for Payroll, and has been removed',

HD_TICKET_CHANGE.COMMENT_LOC = 'Info is for Payroll, and has been removed',


Then when I sent in a new test ticket.. it seemed to work & I was very excited!


but then I realized I had overwritten the HD_TICKET_CHANGE.COMMENT & the HD_TICKET_CHANGE.COMMENT_LOC for EVERY ticket in EVERY queue in the WHOLE database!!


Luckily we had a server backup from about an hour earlier... that we restored...


So now my question;


What am I missing in this Update SQL?


update HD_TICKET, HD_TICKET_CHANGE, HD_STATUS as T5

    set HD_TICKET.SUMMARY = 'Info is for Payroll, and has been removed', 

HD_TICKET.HTML_SUMMARY = 'Info is for Payroll, and has been removed',

HD_TICKET.HD_CATEGORY_ID = '980',

HD_TICKET_CHANGE.COMMENT = 'Info is for Payroll, and has been removed',

HD_TICKET_CHANGE.COMMENT_LOC = 'Info is for Payroll, and has been removed',

HD_TICKET.HD_STATUS_ID = T5.ID, 

HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 

HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 

HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), 

HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), 

HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)


  where T5.NAME = 'Closed' and 

HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and 

        (HD_TICKET.ID in (<TICKET_IDS>))


Any helpful suggestion are appreciated..


Thanks


J


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: IgorAngelini 4 months ago
Second Degree Blue Belt
0

Top Answer

I think you forgot to make a relation between HD_TICKET and HD_TICKET_CHANGE


Maybe it just needs


where T5.NAME = 'Closed'

and HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID

and HD_TICKET.ID in (<TICKET_IDS>)

AND HD_TICKET_CHANGE.HD_TICKET_ID  IN (<TICKET_IDS>)

/*I think this will still just add the info to all rows but only for that ticket, I think the line below is also required*/

AND HD_TICKET_CHANGE.COMMENT !="" /*This will select only lines that have comments on them, but will still overwrite the info on it, I think the right thing to do is to add a new comment, but I don't think you can do that with an update query*/


I can't test this, so I really don't know if it works, let me know



Comments:
  • The lines:
    HD_TICKET_CHANGE.COMMENT = 'Info is for Payroll, and has been removed',

    HD_TICKET_CHANGE.COMMENT_LOC = 'Info is for Payroll, and has been removed',
    adds the info into the fields..

    I thought that maybe this would work:?
    update HD_TICKET, HD_TICKET_CHANGE as T4, HD_STATUS as T5
    set HD_TICKET.SUMMARY = 'Info is for Payroll, and has been removed',
    HD_TICKET.HTML_SUMMARY = 'Info is for Payroll, and has been removed',
    HD_TICKET.HD_CATEGORY_ID = '980',
    T4.COMMENT = 'Info is for Payroll, and has been removed',
    T4.COMMENT_LOC = 'Info is for Payroll, and has been removed',


    HD_TICKET.HD_STATUS_ID = T5.ID,
    HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
    HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
    HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
    HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
    HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)

    where T5.NAME = 'Closed' and
    HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
    T4.HD_TICKET_ID = HD_TICKET.ID and
    (HD_TICKET.ID in (<TICKET_IDS>))


    What do you think?

    Thanks
    J - jct134 4 months ago
    • I think so, it's basically the same as mine on:

      T4.HD_TICKET_ID = HD_TICKET.ID
      vs.
      HD_TICKET_CHANGE.HD_TICKET_ID IN (<TICKET_IDS>)

      but I also think that the last line is important, read the comments:

      /*I think this will still just add the info to ALL rows but only FOR THAT TICKET, I think the line below is also required*/

      AND HD_TICKET_CHANGE.COMMENT !="" /*This will SELECT ONLY ROWS THAT HAVE COMMENTS ON THEM, but will still overwrite the info on it, I think the right thing to do is to add a new comment, but I don't think you can do that with an update query*/


      https://prnt.sc/26xp8d7 this is the content of the HD_TICKET_CHANGE Table, see that not all ROWS have a COMMENT value? If you do not specify, HD_TICKET_CHANGE.COMMENT !="" I think it will just write "Info is for Payroll, and has been removed" on ALL ROWS that have the T4.HD_TICKET_ID = HD_TICKET.ID - IgorAngelini 4 months ago
      • Going to do some tests either tonight or another night (whatever day my other server guy is avail to restore if I break stuff!) I will let post my results :) thanks for your assistance
        J - jct134 4 months ago
      • Let me know how it goes, thinking about it again, I think is better to use:

        T4.HD_TICKET_ID IN (<TICKET_IDS>)

        Something is telling me that T4.HD_TICKET_ID = HD_TICKET.ID will still overwrite all IDs IF it doesn't see the WHERE from the other table, I'm not that versed in SQL to tell you one way or the other, but the IN (<TICKET_IDS>) will put some redundancy on the where. Maybe try both of them? IDK, your call. - IgorAngelini 4 months ago
      • Was going to try
        T4.HD_TICKET_ID = HD_TICKET.ID IN (<TICKET_IDS>)

        Going to try later tonight 9 hours from now.. I will post my results :)
        Thanks
        J - jct134 4 months ago
      • This was my final Update statement:
        update HD_TICKET, HD_TICKET_CHANGE as T4, HD_STATUS as T5
        set HD_TICKET.SUMMARY = 'Info is for Payroll, and has been removed',
        HD_TICKET.HTML_SUMMARY = 'Info is for Payroll, and has been removed',
        HD_TICKET.HD_CATEGORY_ID = '980',
        T4.COMMENT = 'Info is for Payroll, and has been removed',
        T4.COMMENT_LOC = 'Info is for Payroll, and has been removed',


        HD_TICKET.HD_STATUS_ID = T5.ID,
        HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
        HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
        HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
        HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
        HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)

        where T5.NAME = 'Closed' and
        HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
        T4.HD_TICKET_ID = HD_TICKET.ID and
        (HD_TICKET.ID in (<TICKET_IDS>))

        I first tried it with :
        T4.HD_TICKET_ID = HD_TICKET.ID IN (<TICKET_IDS>) and
        (HD_TICKET.ID in (<TICKET_IDS>))

        However no rows were updated..
        But changing it to: T4.HD_TICKET_ID = HD_TICKET.ID and
        (HD_TICKET.ID in (<TICKET_IDS>)), it then updated 2 ROWS, which I believe was correct.. and I am very happy & I now our payroll dept will be very happy as well!

        Thanks again for your assistance

        J - jct134 3 months ago
      • Perfect! Glad I could help - IgorAngelini 4 months ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ