none
Copy values from one table to another upon conditions. RRS feed

  • Question

  • How can I copy the values from Test1Value  from Test1 to Test2Value in Test2 where Test2.Test1ID = Test1.Test1ID? I don't want to use a cursor.

    CREATE TABLE Test1
    (
        Test1ID int,
        Test1Value nvarchar(100)
    );
    
    CREATE TABLE Test2
    (
        Test2ID int,
        Test1ID int,
        Test2Value nvarchar(100)
    );


    • Edited by Lio1972 Monday, December 2, 2019 9:19 AM
    Monday, December 2, 2019 9:19 AM

Answers

  • Please check if below works. If you also want to insert the new rows, have a look at MERGE.

    UPDATE t2
    SET t2.Test2Value = t1.Test1Value
    FROM Test2 AS t2
    JOIN Test1 AS t1 ON t1.Test1ID = t2.Test1ID


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by Lio1972 Monday, December 2, 2019 9:55 AM
    Monday, December 2, 2019 9:30 AM

All replies

  • Please check if below works. If you also want to insert the new rows, have a look at MERGE.

    UPDATE t2
    SET t2.Test2Value = t1.Test1Value
    FROM Test2 AS t2
    JOIN Test1 AS t1 ON t1.Test1ID = t2.Test1ID


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by Lio1972 Monday, December 2, 2019 9:55 AM
    Monday, December 2, 2019 9:30 AM
  • Join the tables and update the values

    UPDATE Test2
    SET Test2Value = Test1Value
    FROM Test1
         INNER JOIN
         Test2
            ON Test2.Test1ID = Test1.Test1ID


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, December 2, 2019 9:36 AM
  • Merge Test2 t2
     Using Test1 t1  ON t2.Test1ID = t1.Test1ID
     When matched then
     UPDATE  
    SET Test2Value = t1.Test1Value;

    Monday, December 2, 2019 2:25 PM
    Moderator