update 语句

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;