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

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

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?
  • 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.
Please log in to comment

Answers

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')

 

Answered 07/23/2013 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity