K1000: Update Asset Data from Inventory
I need to run a Helpdesk custom rule to update Field_19 on Asset_Data_5 table with the notes from the Inventory table. Does anybody know the correct MySQL syntax version for this as the Asset table also needs to be referenced
Thanks
2 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
11 years ago
This isn't tested but I believe it will work:
UPDATE ASSET_DATA_5 set FIELD_19 =
(select MACHINE.NOTES from MACHINE
JOIN ASSET ON ASSET.NAME = MACHINE.NAME
WHERE ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID)
I can't test this exact statement in our environment because our machine names aren't the same as our asset names. You might want to add another statement for testing purposes to make it something like:
UPDATE ASSET_DATA_5
set FIELD_19 =
(select MACHINE.NOTES from MACHINE
JOIN ASSET ON ASSET.NAME = MACHINE.NAME
WHERE ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID
and MACHINE.NAME = 'testcomputer')
Comments:
-
Thanks for the assistance - aidan_oriordan 11 years ago
UPDATE ASSET_DATA_5
set FIELD_19 = [something]
WHERE ID = [id to be updated]
the [something] might end up being a sub query to get your data from the MACHINE table. How are you planning to match the ASSET_DATA_5.ID to the machine ID that you're pulling from? - chucksteel 11 years ago
There is no direct link between MACHINE and ASSET_DATA_5 as far as i'm aware so the match i'm trying to create is MACHINE.NAME = ASSET.NAME and ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID. - aidan_oriordan 11 years ago