2021-06-24

标题oracle数据库批量更新两个表中的数据

需求：oracle数据库批量更新表中的数据

现有两张表A和B，其中A表字段：id，name，year。B表字段：id，name，year。其中A表的id与B表的id相对应，但是A表的year与B表的year不同。需要将A表中的year同步到B表中

create table A_TEST
(
  id   NUMBER not null,
  name VARCHAR2(12) not null,
  year NUMBER
);
create table B_TEST
(
  id   NUMBER not null,
  name VARCHAR2(12) not null,
  year NUMBER
);

插入数据

insert into A_TEST values(1,'liuwei',20);
insert into A_TEST values(2,'zhangbin',21);
insert into A_TEST values(3,'fuguo',20);
commit;
 
insert into B_TEST values(1,'zhangbin',30);
insert into B_TEST values(2,'yihe',33);
insert into B_TEST values(3'fuguo',34);
commit;

查询语句

UPDATE A_TEST A
SET A.YEAR = (
SELECT B.ID FROM B_TEST B
WHERE
A.ID = B.ID
)
WHERE EXISTS(
SELECT 1
FROM B_TEST B
WHERE B.ID = A.ID
)

WHERE EXISTS(
SELECT 1
FROM B_TEST B
WHERE B.ID = A.ID
)存在就更新，否则不更新