在oracle数据库中 sys_userdept与sys_dept两个表,想把sys_userdept.id的值更新为sys_dept.id(整列数据)update sys_userdept
set deptid = (select id from sys_dept)
报错:ORA-01427: 单行子查询返回多个行
请帮帮修改一下语句!~
简而言之:查询一个表的一列值,更新到另一个表的一列值
set deptid = (select id from sys_dept)
报错:ORA-01427: 单行子查询返回多个行
请帮帮修改一下语句!~
简而言之:查询一个表的一列值,更新到另一个表的一列值
--批量更新
--1.使用merge into:高效
merge into sys_userdept su
using sys_dept sd on
(su.col=sd.col)
when matched then
update
set su.deptid=sd.id
--2.,一般的更新,col为两表的连接属性
update sys_userdept su
set deptid = (
select id from sys_dept sd
where su.col=sd.col)
update sys_userdept a
set deptid = (select id from sys_dept b where a.id=b.id
)
必须改成一对一对返回结果。,用sys_dept表的主键与sys_userdept中的相关字段进行关联就行了。
set deptid = (select id from sys_dept b where a.KEYS=b.KEYS)KEYS是主键
set deptid = (select id from sys_dept b where a.KEYS=b.KEYS)
where exists (select id from sys_dept b where a.KEYS=b.KEYS)
where 条件要……………………
set deptid = (select id from sys_dept b where a.KEYS=b.KEYS)
where exists (select 1 from sys_dept b where a.KEYS=b.KEYS)
-- KEYS 是2个表的连接字段, 使用exists 效率高点
set deptid = (
select id from sys_dept b
where a.col=b.col)
如果没有where条件更新的话可以用下面的语句
update sys_userdept
set deptid = (select max(id) from sys_dept);
-- or
update sys_userdept
set deptid = (select id from sys_dept where rownum<=1);
set deptid = (select id from (select id from sys_dept group by id) b
where a.id=b.id)
where exists
(select 1 from (select id from sys_dept group by id) b
where a.id=b.id
)
应该是sys_dept表中存在多条记录与sys_userdept 对应
set deptid = (select id from sys_dept b
where a.id=b.id
------------------
这个效率低,100万条需要5分钟;
--------------------------------
merge into sys_userdept su
using sys_dept sd on
(su.col=sd.col)
when matched then
update
set su.deptid=sd.id
--------------------------
这个没有试过,下次试下,学习了