create table ljwjk.info_lj_taizhang
(
JH varchar(40),
SBSJ varchar(40)
)
insert into test1 select '夏461','2010-08-15'
insert into test1 select '义97-斜18','2008-08-25'create table ljwjk.lj_taizhang
(
JH varchar(10),
SBRQ DATE
)
insert into test2 select '夏461',' '
insert into test2 select '义97-斜18',' '要得到这样的报表:
表1:ljwjk.info_lj_taizhang
JH SBSJ JH SBSJ
'夏461','2010-08-15' '义97-斜18','2008-08-25'
表2:ljwjk.lj_taizhang
JH SBRQ JH SBRQ
'夏461', ' ' '义97-斜18', ''
SQL如果这两个表的井号相同,就把表ljwjk.lj_taizhang中SBRQ为空的被ljwjk.info_lj_taizhang
表的SBSJ所替换.
所写语句:
update ljwjk.lj_taizhang set sbrq=(select distinct sbsj from ljwjk.info_lj_taizhang,ljwjk.lj_taizhang where info_lj_taizhang.jh=lj_taizhang.jh) where sbrq is null;
报错:ORA-01427: single-row subquery returns more than one row
请指导一下
(
JH varchar(40),
SBSJ varchar(40)
)
insert into test1 select '夏461','2010-08-15'
insert into test1 select '义97-斜18','2008-08-25'create table ljwjk.lj_taizhang
(
JH varchar(10),
SBRQ DATE
)
insert into test2 select '夏461',' '
insert into test2 select '义97-斜18',' '要得到这样的报表:
表1:ljwjk.info_lj_taizhang
JH SBSJ JH SBSJ
'夏461','2010-08-15' '义97-斜18','2008-08-25'
表2:ljwjk.lj_taizhang
JH SBRQ JH SBRQ
'夏461', ' ' '义97-斜18', ''
SQL如果这两个表的井号相同,就把表ljwjk.lj_taizhang中SBRQ为空的被ljwjk.info_lj_taizhang
表的SBSJ所替换.
所写语句:
update ljwjk.lj_taizhang set sbrq=(select distinct sbsj from ljwjk.info_lj_taizhang,ljwjk.lj_taizhang where info_lj_taizhang.jh=lj_taizhang.jh) where sbrq is null;
报错:ORA-01427: single-row subquery returns more than one row
请指导一下
set sbrq=b.sbrq
from ljwjk.lj_taizhang a,
ljwjk.info_lj_taizhang b
where a.jh=b.jh
and b.sbrq is null
ljwjk.lj_taizhang
set
sbrq=b.sbrq
from
ljwjk.lj_taizhang a, ljwjk.lj_taizhang b
where
exists(select 1 from ljwjk.lj_taizhang where a.jh=b.jh)
ljwjk.lj_taizhang
set
sbrq=b.sbrq
from
ljwjk.lj_taizhang a, ljwjk.lj_taizhang b
where
exists(select 1 from ljwjk.lj_taizhang where a.jh=b.jh
and
b.sbrq is null
set sbrq=select distinct sbsj
set sbrq=b.sbrq
from ljwjk.lj_taizhang a,
ljwjk.info_lj_taizhang b
where a.jh=b.jh
and b.sbrq is null ;
update
ljwjk.lj_taizhang
set
sbrq=b.sbrq
from
ljwjk.lj_taizhang a, ljwjk.lj_taizhang b
where
exists(select 1 from ljwjk.lj_taizhang where a.jh=b.jh
and
b.sbrq is
报错:ora=00933
我写的update中的这条语句可以执行,一加上update就不执行;sbsj的格式是YYYYMMDD SBRQ的格式是YYYYMMDD HH::MM::NN,如何按这两种格式转换?