Update table using values from another table in SQL Server

  • I have 2 table in my database.

    Table #1

    -------------------------------------------------------------------------
    | name | family | phone | email | gender | phone2 | address | birthdate |
    -------------------------------------------------------------------------
    

    Table #2

    -----------------------------------------
    | gender | address | phone | birthdate |
    -----------------------------------------
    

    in table #1 columns address and phone2 is empty and columns gender and birthdate values is same as table #2.

    How can I read data from table #2 and update address and phone2 in table #1 with values from table #2 address and phone columns when gender and birthdate is the same in each row?

    for example: this is some data in Table #1

    -------------------------------------------------------------------------
    | name | family | phone | email | gender | phone2 | address | birthdate |
    -------------------------------------------------------------------------
    | john | doe    | 12345| [email protected]| Male  |         |         | 1980-01-01|
    -------------------------------------------------------------------------
    | mike | clark  | 65432| [email protected]| Male  |         |         | 1990-01-01|
    -------------------------------------------------------------------------
    | Sara | King   | 875465| [email protected]|Female|         |         | 1970-01-01|
    -------------------------------------------------------------------------
    

    and here is some data in table #2

    -----------------------------------------
    | gender | address | phone | birthdate  |
    -----------------------------------------
    | Male   | 1704test|0457852|1980-01-01  |
    -----------------------------------------
    | Female | 1705abcs|0986532|1970-01-01  |
    -----------------------------------------
    | Male   | 1602cyzd|0326589|1990-01-01  |
    -----------------------------------------
    

    I want to update table #1 with data from table #2 and check gender and birthdate and make table #1 like

    -------------------------------------------------------------------------
    | name | family | phone | email | gender | phone2 | address | birthdate |
    -------------------------------------------------------------------------
    | john | doe    | 12345| [email protected]| Male   |0457852 |1704test | 1980-01-01|
    -------------------------------------------------------------------------
    | mike | clark  | 65432| [email protected]| Male   |0326589  |1602cyzd| 1990-01-01|
    -------------------------------------------------------------------------
    | Sara | King   | 875465| [email protected]|Female |0986532  |1705abcs| 1970-01-01|
    -------------------------------------------------------------------------
    

    How can I do this?

    And what if there are 2 or more people with same gender and birthdate? Which phone and address (of the many) should be copied?

    its not possible, this is just test table, in my real data its not possible to same person have same values.

    If it is really not possible, i.e. if there is a `UNIQUE` constraint on `table2 (gender, birthdate)`, you should add that info in the question.

  • There are quite a few ways to achieve your desired results.

    Undeterministic methods

    (in the event that many rows in table 2 match one in table 1)

    UPDATE T1
    SET    address = T2.address,
           phone2 = T2.phone
    FROM   #Table1 T1
           JOIN #Table2 T2
             ON T1.gender = T2.gender
                AND T1.birthdate = T2.birthdate
    

    Or a slightly more concise form

    UPDATE #Table1
    SET    address = #Table2.address,
           phone2 = #Table2.phone
    FROM   #Table2
    WHERE  #Table2.gender = #Table1.gender
           AND #Table2.birthdate = #Table1.birthdate 
    

    Or with a CTE

    WITH CTE
         AS (SELECT T1.address AS tgt_address,
                    T1.phone2  AS tgt_phone,
                    T2.address AS source_address,
                    T2.phone   AS source_phone
             FROM   #Table1 T1
                    INNER JOIN #Table2 T2
                      ON T1.gender = T2.gender
                         AND T1.birthdate = T2.birthdate)
    UPDATE CTE
    SET    tgt_address = source_address,
           tgt_phone = source_phone 
    

    Deterministic methods

    MERGE would throw an error rather than accept non deterministic results

    MERGE #Table1 T1
    USING #Table2 T2
    ON T1.gender = T2.gender
       AND T1.birthdate = T2.birthdate
    WHEN MATCHED THEN
      UPDATE SET address = T2.address,
                 phone2 = T2.phone; 
    

    Or you could pick a specific record if there is more than one match

    With APPLY

    UPDATE T1
    SET    address = T2.address,
           phone2 = T2.phone
    FROM   #Table1 T1
           CROSS APPLY (SELECT TOP 1 *
                        FROM   #Table2 T2
                        WHERE  T1.gender = T2.gender
                               AND T1.birthdate = T2.birthdate
                        ORDER  BY T2.PrimaryKey) T2 
    

    .. Or a CTE

    WITH T2
         AS (SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY gender, birthdate ORDER BY primarykey) AS RN
             FROM   #Table2)
    UPDATE T1
    SET    address = T2.address,
           phone2 = T2.phone
    FROM   #Table1 T1
           JOIN T2
             ON T1.gender = T2.gender
                AND T1.birthdate = T2.birthdate
                AND T2.RN = 1;
    

    Thanks for your great help! I Have 2 question: 1) I think it's simple way to do this, I think this way is decrease performance and if I have about 50milion record this way is very slow, do you agree? 2) in this way, if I want join 2table and some columns in table #2 does not exist in table #1 I got any error? for example if I have color column in table #2 and its not exist on table #1, join process got error or just join columns exist in 2tables? Thanks again...

    @JohnDoe if you have a performance question ask a new question and supply details of the table sizes, structures, indexes and execution plans. I don't understand what you are asking in point 2 please edit your question and supply example table structures that demonstrate the issue you are asking about.

    @JohnDoe: If you mean by column you mean a column *value* (in other words, a *matching row*) – when there is no matching row, no error is raised. In case of an inner join (like here), the unmatched row(s) just won't be updated.But if you indeed meant a *column* that exists in one table and doesn't exist in the other, then I believe that's one more new question to ask separately.

    On that first CTE, how does SQL Server know what table to update?

    @RonJohn It knows the source of the columns. i.e. that both `tgt_address` and `tgt_phone` are aliases for columns in `#Table1` - so that is the target for the update.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM