Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Unanswered: Update Rows from another table

    Hi

    I would like update the rows of table 1 with the values in table2 see below based on a matching ID. I don’t want to update the ID field as this would be a violation of the primary key I assume as ID is a primary key, just the FirstName and SecondName fields need updating.

    Table1
    (ID, FirstName, Surname)
    Table 2
    (ID, FirstName, Surname)

    Any ideas?
    Cheers

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The syntax for this is explained in the manual:
    http://msdn2.microsoft.com/en-us/library/ms177523.aspx

    Code:
    UPDATE table1 
    SET firstname = table2.firstname,
        lastname = table2.lastname
    FROM table2 
    WHERE table1.id = table2.id

  3. #3
    Join Date
    Dec 2003
    Posts
    22
    Great thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by shammat
    The syntax for this is explained in the manual:
    http://msdn2.microsoft.com/en-us/library/ms177523.aspx

    Code:
    UPDATE table1 
    SET firstname = table2.firstname,
        lastname = table2.lastname
    FROM table2 
    WHERE table1.id = table2.id
    Isn't that

    Code:
    UPDATE t1 
    SET firstname = t2.firstname,
        lastname = t2.lastname
    FROM table1 t1 INNER JOIN table2 t2 
    WHERE t1.id = t2.id
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2007
    Posts
    96
    Here's a sample script tha examplifies what you are trying to do

    Code:
    -- Declaring the tables
    DECLARE @table1 AS Table(Id Int, FirstName NVarChar(50), Surename NVarChar(50));
    DECLARE @table2 AS Table(Id Int, FirstName NVarChar(50), Surename NVarChar(50));
    
    -- Inserting some dummy data into them
    INSERT INTO @table1
    SELECT 1, 'Joe', 'Smith' UNION ALL
    SELECT 2, 'Bety', 'McSomething';
    
    INSERT INTO @table2
    SELECT 1, 'Joe', 'Smith' UNION ALL
    SELECT 2, 'Bety', 'Smith';
    
    -- Selecting the original table 1 data
    SELECT * FROM @table1;
    
    -- Updating table 1 records based on table 2
    UPDATE @table1
    SET   FirstName	= t2.FirstName
    	, Surename	= t2.Surename
    FROM @table1 t1
    INNER JOIN @table2 t2 ON t1.Id = t2.Id;
    
    -- Selecting table 1 data
    SELECT * FROM @table1;
    Hope this helps.
    Best regards
    Last edited by Diabolic; 08-24-07 at 12:34.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Brett Kaiser
    Isn't that

    Code:
    UPDATE t1 
    SET firstname = t2.firstname,
        lastname = t2.lastname
    FROM table1 t1 INNER JOIN table2 t2 
    WHERE t1.id = t2.id
    I "grew up" in the pre-JOIN area, so that's why I'm using the old syntax (which works just as well).
    But shouldn't your statement be INNER JOIN table2 t2 ON t1.id = t2.id?

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by shammat
    I "grew up" in the pre-JOIN area
    I can never remember if that is near Area 51, or if it is a reference to the reception area of the Kit-Kat Ranch.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •