How can I get sql update from another table?

441    Asked by AnishaDalal in SQL Server , Asked on Sep 29, 2022

I have 2 tables 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 are 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| t@t.com| Male  |         |         | 1980-01-01|
-------------------------------------------------------------------------
| mike | clark  | 65432| x@y.com| Male  |         |         | 1990-01-01|
-------------------------------------------------------------------------
| Sara | King   | 875465| a@b.com|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| t@t.com| Male   |0457852 |1704test | 1980-01-01|
-------------------------------------------------------------------------
| mike | clark  | 65432| x@y.com| Male   |0326589  |1602cyzd| 1990-01-01|
-------------------------------------------------------------------------
| Sara | King   | 875465| a@b.com|Female |0986532  |1705abcs| 1970-01-01|
-------------------------------------------------------------------------

How can I do this?

Answered by Amit raj

To get sql update from another table- There are quite a few ways to achieve your desired results.

Non Deterministic 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, birth date ORDER BY primary key) 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;


Your Answer

Interviews

Parent Categories