/build/static/layout/Breadcrumb_cap_w.png

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
  • The basic syntax would be:
    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 10 years ago
  • i need to update ASSET_DATA_5.FIELD_19 from MACHINE.NOTES.

    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 10 years ago

Answers (1)

Answer Summary:
Posted by: chucksteel 10 years ago
Red Belt
0

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:

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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