Post reply

Updating a column challange

  • September 22, 2008 at 9:42 am

    #193149

    Hello,

    My first post is asking for help, cheeky I know 😛

    Probably a quick easy one for your SQL guru's 😉

    I just want to update a row [ASSET_TABLE].[LOCATION_ID] with the value from the [ASSET_TABLE].[LOCATION_ID] of another ROW where the first ROWs' [ASSET_TABLE].[VIRTUAL_HOST] = [ASSET_TABLE].[ASSET_TAG] of the second ROW.

    I'm trying to update the virtual computer locations with the location of its hosts' location.

    Is this even possible within the same table???

    SELECT [ASSET_TABLE].[LOCATION_ID] FROM [ASSET_TABLE] WHERE [ASSET_TABLE].[VIRTUAL_HOST] = [ASSET_TABLE].[ASSET_TAG]

    This brings back the results I expect, but how do I update the [ASSET_TABLE].[LOCATION_ID] of the row I want to update?

    Thanks in advance for your help people

  • September 22, 2008 at 10:16 am

    #875127

    Hi,

    Something like this should work for you:

    UPDATE asset_table

    SET locationID = (

    SELECT a.locationID

    FROM asset_table a

    WHERE asset_table.virtual_host = a.asset_tag

    )

    Hth


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 22, 2008 at 11:01 am

    #875157

    Thanks for the quick response Ade,

    I've typed it exactly as you presented but it just updates the hosts location_ID to it's own virtual host, which is null as it doesn't have a host!!!:hehe::crazy:

    I want to update the virtual computers location record with it's respective hosts location.

    Thanks for the tip, I'll play with this script as it's doing what I want, just the wrong row.:cool:

  • September 22, 2008 at 11:20 am

    #875168

    A big thanks Ade,

    Actually, looking at it, it does update the virtual computers. In fact it updates all rows which fixes the virtual computers but destroy the location id's of the host :crazy:

  • September 22, 2008 at 11:31 am

    #875175

    A huge big thanks Ade,

    That's the query, worked a treat after I added a WHERE clause outside the brackets

    Adrian Nichols (9/22/2008)

    Hi,

    Something like this should work for you:

    UPDATE asset_table

    SET locationID = (

    SELECT a.locationID

    FROM asset_table a

    WHERE asset_table.virtual_host = a.asset_tag

    )

    WHERE asset_table.virtual_host != NULL

    Hth

  • September 23, 2008 at 2:06 am

    #875467

    I should've done that! 😀


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply