none
TSQL Update statement with JOIN

    Question

  • I have 3 related tables - I want to update a ‘name’ field in one table to be the same as an associated group ‘name’ field of related table.  Here is the table structure:

    CREATE TABLE Group
    (
    	GroupId	INT NOT NULL IDENTITY( 1, 1 ) PRIMARY KEY,
    	Name NVARCHAR( 255 ) NULL
    )
    GO
    
    CREATE TABLE Item
    (
    	ItemId	INT NOT NULL IDENTITY( 1, 1 ) PRIMARY KEY,
    	Name NVARCHAR( 255 ) NULL
    )
    GO
    
    CREATE TABLE ItemGroup
    (
    	GroupId			INT NOT NULL,
    	ItemId			INT NOT NULL,
    	PRIMARY KEY ( GroupId, ItemId ),
    	FOREIGN KEY ( GroupId )	REFERENCES Group( GroupId ) ON DELETE CASCADE,
    	FOREIGN KEY ( ItemId )	REFERENCES Item( ItemId )	ON DELETE CASCADE
    )
    GO

    This is what I have tried so far, but I am getting syntax errors:

    UPDATE	Item i
    JOIN	ItemGroup ig
      ON	i.ItemId = ig.ItemId
    JOIN	Group g
      ON	ig.GroupId = g.GroupId
    SET	g.Name = i.Name
    
    Can anyone point me in the right direction on how to do a mass update with joins?


    MT
    Monday, October 19, 2009 11:46 PM

Answers

  • UPDATE Item i SET g.Name = i.Name -- most logically would be SET i.Name = g.Name instead
    from Item I
    INNER JOIN ItemGroup ig
      ON i.ItemId = ig.ItemId
    INNER JOIN Group g
      ON ig.GroupId = g.GroupId


    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    • Marked as answer by dccMike Tuesday, October 20, 2009 12:03 AM
    • Unmarked as answer by dccMike Tuesday, October 20, 2009 3:40 PM
    • Marked as answer by dccMike Tuesday, October 20, 2009 3:49 PM
    Monday, October 19, 2009 11:48 PM

All replies

  • UPDATE Item i SET g.Name = i.Name -- most logically would be SET i.Name = g.Name instead
    from Item I
    INNER JOIN ItemGroup ig
      ON i.ItemId = ig.ItemId
    INNER JOIN Group g
      ON ig.GroupId = g.GroupId


    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    • Marked as answer by dccMike Tuesday, October 20, 2009 12:03 AM
    • Unmarked as answer by dccMike Tuesday, October 20, 2009 3:40 PM
    • Marked as answer by dccMike Tuesday, October 20, 2009 3:49 PM
    Monday, October 19, 2009 11:48 PM
  • UPDATE	Item
    SET	i.Name = g.Name
    FROM	Item i
    INNER JOIN ItemGroup ig
      ON i.ItemId = ig.ItemId
    INNER JOIN [Group] g
      ON ig.GroupId = g.GroupId
    Thank you Naom!  The above has just a couple slight syntax changes to make it work.
    MT
    Tuesday, October 20, 2009 12:03 AM
  • Now that I actually have executed the code - I am getting an odd error:
    ----------------------
    The multi-part identifier "i.Name" could not be bound.
    ----------------------

    Any ideas?

    MT
    Tuesday, October 20, 2009 3:32 PM
  • I had to use fully qualified names (no alias) to get around that issue like this:
    UPDATE	Item
    SET	Item.Name = [Group].Name
    FROM	Item
    INNER JOIN ItemGroup
      ON Item.ItemId = ItemGroup.ItemId
    INNER JOIN [Group]
      ON ItemGroup.GroupId = [Group].GroupId
    Thanks for your help!

    MT
    Tuesday, October 20, 2009 3:54 PM
  • There was a typo I didn't notice.

    UPDATE i SET i.Name = g.Name

    from Item I
           INNER JOIN ItemGroup ig
           ON i.ItemId = ig.ItemId
           INNER JOIN Group g
           ON ig.GroupId = g.GroupId
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog