使用 SQL Server 中另一個表中的值更新表

我的數據庫中有 2 個表。

表格1

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

表#2

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

在表#1 中的列地址和電話2為空，列的性別和出生日期值與表#2 相同。

當每行中的性別和出生日期相同時，如何從表#2 中讀取數據並使用表#2 地址和電話列中的值更新表# 1****中的地址和電話2 ？

例如：這是表 #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|
-------------------------------------------------------------------------

這是表#2中的一些數據

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

我想用表 #2 中的數據更新表 #1 並檢查性別和出生日期並使表 #1 像

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

我怎樣才能做到這一點？

有很多方法可以實現您想要的結果。

不確定的方法

（如果表 2 中的許多行與表 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

或者更簡潔的形式

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

或使用 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 

確定性方法

MERGE會拋出錯誤而不是接受不確定的結果

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; 

或者，如果有多個匹配項，您可以選擇特定記錄

和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 

.. 或 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;

引用自：https://dba.stackexchange.com/questions/138901