#概述:用一个表中的字段去更新另外一个表中的字段
#Oracle
准备阶段
创建2个表,插入合适的数据
--创建2个表
create table city(
code varchar2(3),
name varchar2(10)
);
create table people(
pp_id number(1),
pp_name varchar2(10),
city_code varchar2(3),
city_name varchar2(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 all into people values(1,'john','001','北京')
into people values(2,'timo','002','')
into people values(3,'张三','003','合肥')
into people values(4,'李四','008','')
into people values(5,'王二麻','009','黑龙江')
select * from dual;
创建好的2个表如下所示:
SQL查询
要求:根据city表的code和name,更新people的city_name
方法1:子查询
--方法1:子查询
update people p
set city_name = (
select name
from city
where code = p.city_code);
结论: 1,代码对应的城市更新,对应错误的更正;
2,city表中没有的城市,在people表里全被更新为null。
方法2:子查询(优化)
update people p
set city_name = (
select name
from city
where code = p.city_code)
where exists (
select 1
from city
where code = p.city_code);
结论: 1,代码对应的城市更新,对应错误的更正;
2,city表中没有的城市,在people表里保持原数据,不会被清空。
方法3:merge 并入数据
merge into 目标表
using 来源
on (关联条件)
when matched then --关联上的数据
update
set 目标表.列=来源表.列,目标表.列=来源表.列,……
whee 条件
when not matched then --关联不上的数据
insert (目标表.列,目标表.列,……)
values (来源表.列,来源表.列,……)
where 条件;
注意:
1、on (关联条件)其中括号()必须有。
2、when matched then 和 when not matched then 可以只保留一个。
3、update where 和 insert where 没有任何关系。
4、set 里面不允许出现 on 中的列。
5、insert 中的目标表.列、来源表.列,数量要相等,数据类型一一对应。
--merge into 并入数据
merge into people p
using city c
on (p.city_code = c.code)
when matched then
update
set p.city_name = c.name;
结论: 1,代码对应的城市更新,对应错误的更正;
2,city表中没有的城市,在people表里保持原数据,不会被清空。
解决问题:ORA-01427:单行子查询返回多个行
insert into city values('002','大西北');
当city表中,存在2个一样的code,对应不同的城市,需选取任一城市更新的时候
update people p
set city_name = (
select max(name)
from city
where code = p.city_code)
where exists (
select 1
from city
where code = p.city_code);
--或者
merge into people p
using (select c.code ,max(c.name) name2
from city c
group by c.code ) cc
on (p.city_code = cc.code)
when matched then
update
set p.city_name = cc.name2;
- 上述写法在
using
后面构造了一个新的cc表,但一定要对name做出处理,如果是varchar类型,可以选择 max,min等函数,如果number类型,可以使用sum,avg等函数,总之,要对name做出处理(对应多个的时候,到底要哪个?最大的还是最小的),新的cc是一个code对应一个name。
结论: 1,代码对应的城市更新,对应错误的更正;
2,一个code对应多个城市名的取其中一个更新;
3,city表中没有的城市,在people表里保持原数据,不会被清空。
解决问题:两表关联更新,同时更新多个列
update customers a -- 使用别名
set (city_name,customer_type)=(select b.city_name,b.customer_type
from tmp_cust_city b
where b.customer_id=a.customer_id)
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id);
--或者
merge into customers a
using tmp_cust_city b
on (a.customer_id = b.customer_id)
when matched then
update
set a.city_name = b.city_name,a.customer_type = b.customer_type;
#MySQL
update people p, city c
set p.city_name = c.name
where p.city_code = c.code;
结论:1,代码对应的城市更新,对应错误的更正;
2,city表中没有的城市,在people表里保持原数据,不会被清空。
update people p left join city c
ON p.city_code=c.`code`
SET p.city_name=c.`name`;
结论: 1,代码对应的城市更新,对应错误的更正;
2,city表中没有的城市,在people表里全被更新为null。
其实update就可以分为外连接和内连接,使用之前一定要想清楚,否则会导致主表的数据被清除。
#SqlServer
update A
set A1=B.B1,A2=B.B2,A3=B.B3,A4=B.B4
from A,B
where A.AID=B.BID
参考:
Oracle中 如何用一个表的数据更新另一个表中的数据 - kangkaii - 博客园 (cnblogs.com)