Help with SQL Update statement
I have this update in a ticket rule -
update HD_TICKET T
JOIN ASSET A ON A.ID=T.ASSET_ID
set CUSTOM_FIELD_VALUE17 = (SELECT group_concat(DISTINCT ASSET.NAME) FROM ASSET WHERE ASSET.ID IN (SELECT ASSET_ID FROM ASSET_ASSOCIATION WHERE ASSET_FIELD_ID='10054' AND ASSOCIATED_ASSET_ID = T.ASSET_ID))
WHERE T.HD_QUEUE_ID=27
AND (T.ID in (<TICKET_IDS>))
When I run the (SELECT group_concat. . ." statement, separately in work bench I get exactly what I was expecting. When used in a ticket rule, it is setting the field to 0. No errors noted in the rule screen, it simply reads the usual, selected 1 row, updated 1 row. The sub query does return more than one row. Because of this I have IN in place of = prior to the sub query.
From what I find via the Google, it is better to use a JOIN in this instance but I haven't be able to change the select with sub query to a functional JOIN.
Thoughts / Suggestions would be most appreciated. Thank you
update HD_TICKET T
JOIN ASSET A ON A.ID=T.ASSET_ID
set CUSTOM_FIELD_VALUE17 = (SELECT group_concat(DISTINCT ASSET.NAME) FROM ASSET WHERE ASSET.ID IN (SELECT ASSET_ID FROM ASSET_ASSOCIATION WHERE ASSET_FIELD_ID='10054' AND ASSOCIATED_ASSET_ID = T.ASSET_ID))
WHERE T.HD_QUEUE_ID=27
AND (T.ID in (<TICKET_IDS>))
When I run the (SELECT group_concat. . ." statement, separately in work bench I get exactly what I was expecting. When used in a ticket rule, it is setting the field to 0. No errors noted in the rule screen, it simply reads the usual, selected 1 row, updated 1 row. The sub query does return more than one row. Because of this I have IN in place of = prior to the sub query.
From what I find via the Google, it is better to use a JOIN in this instance but I haven't be able to change the select with sub query to a functional JOIN.
Thoughts / Suggestions would be most appreciated. Thank you
3 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
I’ve managed to create a series of connected logical assets. In brief, top asset (A) can have 0 or many sub-assets (B). Each B asset has several connected assets, via field type. This works well so far. Because the HD_TICKET Asset field (ASSET_ID) searches ALL asset types, I moved the selection of the top level asset to a custom_field. When the user selects the top level asset, and clicks Apply/Save, a ticket rule looks up the asset ID and sets it in the HD_TICKET.ASSET_ID field.
In this ticket rule, I am looking up any/all of the sub- assets tied to the top level asset, then setting the results in a multi-select custom_field. The group_concat works well to format the results in a comma separated output that fits nicely in the multi-select field. Connected assets connect via the asset_assocation table and can be targeted via the asset_field_id number.
Thus, CUSTOM_FIELD_VALUE17 is the multi_select field, I’m trying to drop the grouped asset.names in. I’m grabbing those names based on HD_TICKET.ASSET_ID by looking at the ASSET_ASSOCIATION between the two. - jmarotto 7 years ago
At this point I’ve had to temporarily set this aside and continue with other parts of the queue. - jmarotto 7 years ago
I can't set up your scenario here by now, but I often had success with linked queries when I completely capsuled the subqueries (which may be even more unperformant, by the way), but you might want to try:
update HD_TICKET T
JOIN ASSET A ON A.ID=T.ASSET_ID
set CUSTOM_FIELD_VALUE17 = (SELECT x.out FROM (SELECT group_concat(DISTINCT ASSET.NAME) AS out FROM ASSET WHERE ASSET.ID IN (SELECT ASSET_ID FROM ASSET_ASSOCIATION WHERE ASSET_FIELD_ID='10054' AND ASSOCIATED_ASSET_ID = T.ASSET_ID)) AS x)
WHERE T.HD_QUEUE_ID=27
AND t.CUSTOM_FIELD_VALUE17 = ''
AND (T.ID in (<TICKET_IDS>))
(edit)
I just added a WHERE condition to avoid overwriting CUSTOM_FIELD_VALUE17 if a value has already been set there. - chrpetri 6 years ago