作者：可可伦-惊叹号 | 来源：互联网 | 2014-07-09 16:02
oracleupdate语句简析Sql代码createtableTEST_EMPLOYEES(IDNUMBER,NAMENVARCHAR2(50),SALARYNUMBER);insertintoTEST_EMPLOYEES(ID,NAME,SALARY)values(1,'张三',80
oracle update语句简析
Sql代码
create table TEST_EMPLOYEES(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (1, &＃39;张三&＃39;, 8000);
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (2, &＃39;李四&＃39;, 7000);
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (3, &＃39;王五&＃39;, 9000);
create table TEST_EMPLOYEES2(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (1, &＃39;张三&＃39;, 8000);
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (2, &＃39;李四&＃39;, 17000);
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (3, &＃39;王五&＃39;, 19000);
www.2cto.com
Sql代码
update test_employees te
set salary =
(select te2.salary
from test_employees2 te2
where te.id = te2.id);
Sql代码
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语句的另外一种写法：注，两个表必须要有主键，没有会导致查询出的结果集无主键，会提示 “无法修改与非键值保存表对应的列”
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;
www.2cto.com
同时set也可以是多列，如下：
Sql代码
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;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 
