How to Update the table using values from another table in SQL Server?

Question

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| 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?

Benjamin Moore · Accepted Answer

There are quite a few ways to achieve your desired outcomes. Nondeterministic 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.birthdateOr 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.birthdateOr 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_phoneDeterministic 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;

How to Update the table using values from another table in SQL Server?

About this question

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

SQL Server Tutorials

SQL Server Interview Q&A

Latest SQL Server Blogs

Want answers to more SQL Server questions?

Stay in the loop — it’s free

You’re on the list!