create table TEST_EMPLOYEES(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (1, '张三', 8000);
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (2, '李四', 7000);
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (3, '王五', 9000);
create table TEST_EMPLOYEES2(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (1, '张三', 8000);
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (2, '李四', 17000);
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (3, '王五', 19000);
update test_employees te
set salary =
(select te2.salary
from test_employees2 te2
where te.id = te2.id);
update test_employees te
set (te.name, te.salary) =
(select te2.name, te2.salary
from test_employees2 te2
where te.id = te2.id
and NVL(te.salary, 0) != nvl(te2.salary, 0))
where exists (select te2.salary
from test_employees2 te2
where te.id = te2.id
and nvl(te.salary, 0) != te2.salary);
注:update 的 where 条件必须要,否则当and NVL(te.salary, 0) = nvl(te2.salary, 0)) 都相等时,返回的结果集为空,update会更新test_employees全表的name,salary为空
上边SQL语句的另外一种写法:注,两个表必须要有主键,没有会导致查询出的结果集无主键,会提示 “无法修改与非键值保存表对应的列”
update (select te.salary, te2.salary new_salary
from test_employees te, test_employees2 te2
where te.id = te2.id
and te.salary != te2.salary)
set salary = new_salary;
同时set也可以是多列,如下:
update (select te.salary, te2.salary new_salary, te.name, te2.name new_name
from test_employees te, test_employees2 te2
where te.id = te2.id
and te.salary != te2.salary)
set salary = new_salary, name = new_name;