update(select a.hno,b.* from tablea a,tableb b
where a.sno=b.sno and b.htel is null)t
set htel=(
select d.htel from
tablea c,tableb d
where c.sno=d.sno
and c.newflg=0
and t.hno=c.hno
and rownum<2
);
tablea的sno需要有唯一约束。这应该不是问题,tablea和tableb的sno都是主键吧
where a.sno=b.sno and b.htel is null)t
set htel=(
select d.htel from
tablea c,tableb d
where c.sno=d.sno
and c.newflg=0
and t.hno=c.hno
and rownum<2
);
tablea的sno需要有唯一约束。这应该不是问题,tablea和tableb的sno都是主键吧
set a.Htel = (select b.Htel,
c.Hno
from tablea b,table c
where a.Hno = '1'
and a.Sno = c.Sno
and a.Sno = b.Sno)
where a.Htel is null;
set a.Htel = (select b.Htel,
c.Hno
from tablea b,table c
where a.Hno = '1'
and a.Sno = c.Sno
and a.Sno = b.Sno)
update (
SELECT W.Htel AS AHtel,Q.Htel as BHtel
FROM TableA W ,
TableB Q
WHERE W.Sno=Q.Sno
and q.Htel is not null
) y4
set y4.AHtel=y4.BHtel
using (select * from table_2 where sno exist (select sno from table_1) and tel_no is not null) b
on a.sno=b.sno
when matched then
update set
a.tel_no=b.tel_no;
commit;
具体你在改下吧
SET htel = (SELECT DISTINCT b.htel
FROM tablea a,
tableb b,
(SELECT aa.sno, aa.hno
FROM tablea aa, tableb bb
WHERE aa.sno = bb.sno
AND aa.newflag = 1
AND bb.htel IS NULL) tt
WHERE a.sno = b.sno
AND b.htel IS NOT NULL
AND tt.hno = a.hno
AND tt.sno = t.sno)
WHERE t.htel IS NULL;
建议在htel 上设置一个默认值,比如0, 不然is null和is not null会使该字段上的index无效.
UPDATE tableb t
SET htel = (SELECT b.htel
FROM tablea a,
tableb b,
(SELECT aa.sno, aa.hno
FROM tablea aa, tableb bb
WHERE aa.sno = bb.sno
AND aa.newflag = 1
AND bb.htel IS NULL) tt
WHERE a.sno = b.sno
AND b.htel IS NOT NULL
AND tt.hno = a.hno
AND tt.sno = t.sno
AND rownum = 1)
WHERE t.htel IS NULL;