createtable student (
id string,
age string,
name string,
dt string
);insertintotable student values("1","11","zhao","20140101"),("2","22","qian","20140102"),("3","33","sun","20140103"),("4","44","li","20140104");select*from student;
现有最新的学生表student_temp
数据较原有student的表,数据有新增也有修改
createtable student_temp(
id string,
age string,
name string,
dt string
);insertintotable student_temp values("1","11","zhao","20140101")-- 原本数据,("2","999","test","20220323")-- 改动,("3","999","test","20220323")-- 改动,("4","44","li","20140104")-- 原本数据,("5","55","wang","20140105")-- 新增数据;select*from student_temp;
查看full outer join效果
select*from student_temp a fullouterjoin student b on a.id = b.id;
开始更新
没有则新增,有则覆盖
SELECT student_temp.id
,coalesce(student_temp.age,student.age)as age
,student_temp.name
,coalesce(student_temp.dt,student.dt)as dt
FROM student_temp
FULLOUTERJOIN student
ON student_temp.id = student.id
;