locked
update table from same table RRS feed

  • Question

  • table A has 1000 records and 5 columns

    table B has exacly same records except for 1 column

    want to update table A from table B( only 1 column which has different values).


    SQL Server DBA

    Friday, November 4, 2016 6:53 AM

All replies

  • Hello,

    Please post tables DDL, some sample data as DML and the expected result; see POSTING TIPS - Code, Images, Hyperlinks, Details


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 4, 2016 6:58 AM
  • Hey Zeal,

    i would use an update join 

    http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server

    Regards

    Michel

    Friday, November 4, 2016 8:51 AM
  • Hi Zeal DBA,

    You may refer to the following script.Also take a look at MERGE (Transact-SQL), it performs insert, update, or delete operations on a target table based on the results of a join with a source table.

    DECLARE @TA TABLE (
    	c1 int, 
    	c2 varchar(10),
    	c5 int
    )
    
    INSERT @TA VALUES
    (1, 'N1', 10),
    (2, 'N2', 20),
    (3, 'N3', 30)
    
    SELECT * FROM @TA
    
    DECLARE @TB TABLE (
    	c1 int, 
    	c2 varchar(10),
    	c5 int
    )
    
    INSERT @TB VALUES
    (1, 'N1', 100),
    (3, 'N3', 300)
    
    SELECT * FROM @TB
    
    --
    UPDATE a
    SET a.c5 = b.c5
    FROM @TA a
    JOIN @TB b ON a.c1 = b.c1
    -- WHERE ...
    
    SELECT * FROM @TA

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 4, 2016 8:55 AM