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;
页:
[1]