[sqlserver] 两表关联更新update (用一个表更新另一个表)

本文介绍了如何通过SQL语句实现两个表的关联更新。首先，创建了city和people两个表，并插入了示例数据。需求是根据city表的code和name字段更新people表的city_name字段。文章提供了两种更新策略：一种是未匹配时保留原有数据，另一种是未匹配时清空原有数据。对于每种策略，文章详细说明了SQL语句的写法，并分析了在city表code唯一和不唯一情况下的执行结果。最后，总结了两种更新方式的应用场景和注意事项。

两表关联更新update (用一个表更新另一个表)

表及数据

• 建表及数据SQL

  --创建2个表
  create table city(
    code varchar(3),
    name nvarchar(10)
  );
   
  create table people(
    pp_id int,
    pp_name nvarchar(10),
    city_code varchar(3),
    city_name nvarchar(10)
  );
   
  --插入合适的数据，该语句一次可插入一行数据
  insert into city values('001','北京');
  insert into city values('002','上海');
  insert into city values('003','深圳');
  insert into city values('004','南京');
  insert into city values('005','广州');
  insert into city values('006','成都');
  insert into city values('007','重庆');
   
  --该语句一次可插入多行数据
  insert into people values(1,'john','001','北京')
  insert into people values(2,'timo','002','')
  insert into people values(3,'张三','003','合肥')
  insert into people values(4,'李四','008','')
  insert into people values(5,'王二麻','009','黑龙江')
  

city表

code	name
1	北京
2	上海
3	深圳
4	南京
5	广州
6	成都
7	重庆

people表

pp_id	pp_name	city_code	city_name
1	john	1	北京
2	timo	2
3	张三	3	合肥
4	李四	8
5	王二麻	9	黑龙江

需求

根据city表的code和name，更新people的city_name。

关联无匹配，保持原数据

update people
set city_name=city.name
-- 输出旧值和新值
OUTPUT deleted.pp_id,deleted.city_name AS OldValue, inserted.city_name AS NewValue
from people,city
where people.city_code=city.code

正常情况：city表的code唯一

执行上面sql，输出：

pp_id	OldValue	NewValue
1	北京	北京
2		上海
3	合肥	深圳

数据修改了三行，结论：

1. 代码对应的城市更新，对应错误的更正
2. city表中没有的城市，在people表里保持原数据，不会被清空

异常情况：city表的code不唯一

插入一个重复code的数据

insert into city values('003','合肥');

恢复people表到初始数据，再次执行上面的更新sql，可以发现与上面返回值一致。

推论：只取先匹配的一个值替换

关联无匹配，清空原数据

update people
set city_name = (
                select name
                from city
                where code = people.city_code)
-- 输出旧值和新值
OUTPUT deleted.pp_id,deleted.city_name AS OldValue, inserted.city_name AS NewValue

正常情况：city表的code唯一

pp_id	OldValue	NewValue
1	北京	北京
2		上海
3	合肥	深圳
4		NULL
5	黑龙江	NULL

数据修改了5行，结论：

1. 代码对应的城市更新，对应错误的更正
2. city表中没有的城市，在people表里全被更新为null

异常情况：city表的code不唯一

报错：子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

修改sql子查询：选取任一城市

update people 
set city_name = (
                select min(name)
                from city
                where code = people.city_code)
-- 输出旧值和新值
OUTPUT deleted.pp_id,deleted.city_name AS OldValue, inserted.city_name AS NewValue

结果：

pp_id	OldValue	NewValue
1	北京	北京
2		上海
3	合肥	合肥
4		NULL
5	黑龙江	NULL

结论

更新时未匹配到关联数据

未匹配，保留原有数据

update people -- 要更新的表
set city_name=city.name --更新表字段
from people,city -- 两张表
where people.city_code=city.code -- 条件

未匹配，清空原有数据

update people 
set city_name = (
                select min(name) -- 重复时匹配其中一个
                from city
                where code = people.city_code)