Hello,

I'm looking for a possibility to use the name from the associated asset in the parent ticket for the child ticket.
I have just started with SQL scripting - hope somebody can help me..

In the ticket rule the SQL select works - the ticket rule give 2 rows back - the follow code row doesn't work and I don't know why.

HD.TICKET.TITLE = (SELECT NAME FROM ORG1.ASSET WHERE ORG1.ASSET.ID =
                  (SELECT ASSET_ID FROM HD_TICKET WHERE HD_TICKET.ID = PARENT_ID))


Thank you!

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • The error message I get is:
    'mysqli error: [1093: Table 'HD_TICKET' is specified twice, both as a target for 'UPDATE' and as a separate source for data]'
Please log in to comment

Answer Chosen by the Author

1
I have a Solution!

Here the whole SQL-update

update HD_TICKET as T
  join HD_STATUS as T5
    on T.HD_QUEUE_ID = T5.HD_QUEUE_ID
  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID
set
  T.HD_STATUS_ID = T5.ID,
  T.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
  T.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
  T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
  T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
  T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT),
  T.TITLE = A.NAME
where T5.NAME = 'Geplant' 
  and T.ID in (<TICKET_IDS>) ;

The SQL-update change the status from new to planed ('Geplant') and change the childticket title to the name from the associated asset in the parentticket.

Answered 09/05/2016 by: svmay
Tenth Degree Black Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity