MSSQL关联更新

1、创建一个数据库

CREATE DATABASE TestBase

2、创建对应的临时表(A表)

GO
CREATE TABLE A(
ID INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(100),
[ADDRESS] VARCHAR(200),
APPROVEDDATE DATE,
REQUIREDDATE DATE
)
GO
INSERT A
SELECT 'AB','AAAA','2021-01-01','' UNION
SELECT 'AC','BBBB',NULL,NULL UNION
SELECT 'AD','CCCC',NULL,NULL UNION
SELECT 'AE','DDDD','2021-07-01','' UNION
SELECT 'AF','EEEE',NULL,NULL 

B表

GO
CREATE TABLE B(
ID INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(100),
[ADDRESS] VARCHAR(200),
APPROVEDDATE DATE,
REQUIREDDATE DATE
)
GO
INSERT B
SELECT 'AC','BBBB','2022-08-23','2019-03-24' UNION
SELECT 'AD','CCCC','2010-10-11','2010-10-13' UNION
SELECT 'AF','EEEE','2015-12-10','2015-10-29' 

3、两张表关联查询结果：

GO
SELECT * FROM A
JOIN B
ON A.NAME=B.NAME

 4、两张表关联后修改A表中对应数据：

GO
UPDATE A SET A.APPROVEDDATE=B.APPROVEDDATE,A.REQUIREDDATE=B.REQUIREDDATE
FROM A,B
WHERE A.NAME=B.NAME
AND A.APPROVEDDATE IS NULL
AND A.REQUIREDDATE IS NULL

注释：A 语法后的“A.APPROVEDDATE”指的是A表中的字段，套在实际表中应用起来就是下面这样子的：

UPDATE UserBase
SET UserBase.APPROVEDDATE=B.APPROVEDDATE,
UserBase.REQUIREDDATE=B.REQUIREDDATE 
FROM UserBase U,B 
WHERE U.NAME=B.NAME 
AND A.APPROVEDDATE IS NULL
AND A.REQUIREDDATE IS NULL

失落时悄悄徘徊，伤感时默默遐想。