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
Answers (1)
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 2 years 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 2 years 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 2 years 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 2 years 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 2 years 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 2 years ago -
Perfect! Glad I could help - IgorAngelini 2 years ago
