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
失落时悄悄徘徊,伤感时默默遐想。