一尘不染
阅读 107
2022-11-18
使用 SQL Server 中另一个表的值更新表
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|
-------------------------------------------------------------------------
我怎样才能做到这一点？
阅读 107
2022-11-18
共1个答案
一尘不染
有很多方法可以达到您想要的结果。
不确定的方法
（如果表 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;
2022-11-18