update user t
set t.sysuserid =
(select a.sysuserid
from user1 a
where t.usercode = a.usercode)
where exists
(select 1 from user1 a where t.usercode = a.usercode)
and length(t.usercode) = 12
在user1 表中可能存在重复的数据,但是执行上面的语句时提示:单行子查询返回多行值,要怎么修改呢?
set t.sysuserid =
(select a.sysuserid
from user1 a
where t.usercode = a.usercode)
where exists
(select 1 from user1 a where t.usercode = a.usercode)
and length(t.usercode) = 12
在user1 表中可能存在重复的数据,但是执行上面的语句时提示:单行子查询返回多行值,要怎么修改呢?
set t.sysuserid =
(select a.sysuserid from user1 a where t.usercode = a.usercode and rownum = 1)
from user t
where exists (select 1 from user1 a where t.usercode = a.usercode) and length(t.usercode) = 12
改成
update user t
set t.sysuserid =
(select a.sysuserid
from user1 a
where t.usercode = a.usercode and rownum<2)
where exists
(select 1 from user1 a where t.usercode = a.usercode)
and length(t.usercode) = 12
改成
update user t
set t.sysuserid =
(select a.sysuserid
from user1 a
where t.usercode = a.usercode and rownum = max(rownum)) ----保证唯一性就OK
where exists
(select 1 from user1 a where t.usercode = a.usercode)
and length(t.usercode) = 12
set t.sysuserid =
(select a.sysuserid
from user1 a
where t.usercode = a.usercode and rownum <2)
where exists
(select 1 from user1 a where t.usercode = a.usercode)
and length(t.usercode) = 12
改成
update user t
set t.sysuserid =
(select distinct a.sysuserid
from user1 a
where t.usercode = a.usercode )
where exists
(select 1 from user1 a where t.usercode = a.usercode)
and length(t.usercode) = 12