Oracle一张表的多个字段更新到另一张表中去

最新推荐文章于 2025-07-14 10:04:57 发布

转载最新推荐文章于 2025-07-14 10:04:57 发布 · 1.7k 阅读

Oracle 专栏收录该内容

10 篇文章

订阅专栏

本文讨论了SQL更新操作中的效率优化方法，并详细解释了如何使用SQL语句进行Oracle数据恢复，包括更新特定字段和恢复部分数据的实例。

假设表a中有多个字段（province ，city）需要从b表获取（两张表的mobile一样），总结了几种写法。

一、update a set a.province=(select province from b where b.mobile=a.mobile);

update a set a.city=(select cityfrom b where b.mobile=a.mobile);

这种写法效率太低，尤其是号码有上万条的时候，所以抛弃。

二、update a set a.province=b.province,a.city=b.city from a inner join b on a.mobile=b.mobile.

或者update a set a.province=b.province,a.city=b.city from a,b where a.mobile=b.mobile.

三、update a inner join b on a.mobile=b.mobile set a.province=b.province,a.city=b.city

注意：第二种和第三种写法在oracle行不通的，老是报错，折腾了好长时间，最后还是用下面的语句解决了问题

四、update a set(a.province,a.city)=(select province,city from b where b.mobile=a.mobile)

其实第四种方法是第一种方法的合并。

项目中写的真实例子：
update m_smsphoneno a set (a.operator,a.province,a.city)=(select OWNER,STATE,CITY from keyaccount.CELLPHONESORT b where substr(a.mobile,1,7)=b.startcode) where a.category=2 and a.city is null; 注：用a.city=null不行的

oracle delete部分数据的恢复方法

select *
from ct_con_truckfeemdyentry as of timestamp to_timestamp('2014-12-25 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
where cfcontractid='vjAAAACGqKh8TWNi';
insert into ct_con_truckfeemdyentry (select * from ct_con_truckfeemdyentry
as of timestamp to_timestamp('2014-12-25 12:00:00','yyyy-mm-dd hh24:mi:ss') where cfcontractid='vjAAAACGqKh8TWNi' );

SELECT * FROM T_ZBD_PERSON

DELETE FROM T_ZBD_PERSON
INSERT INTO T_ZBD_PERSON
(FID,
FNUMBER,
FNAME_L1,
FNAME_L2,
FNAME_L3,
FCONTROLUNITID,
FCREATORID,
FCREATETIME,
FLASTUPDATEUSERID,
FLASTUPDATETIME,
FUSEDSTATUS,
FIDENTITYCARD,
FEASPERSONID,
FADMINORGUNITID,
   fadnumber)
SELECT NEWBOSID('73A28D5D'),
d.FNUMBER,
d.FNAME_L1,
d.FNAME_L2,
d.FNAME_L3,
d.FCONTROLUNITID,
d.FCREATORID,
d.FCREATETIME,
d.FLASTUPDATEUSERID,
d.FLASTUPDATETIME,
1,
d.FIDCARDNO,
D.FID,
           b.fid           ,
           u.fadnumber
FROM T_ORG_POSITION A
INNER JOIN T_ORG_BASEUNIT B
ON B.FID = A.FADMINORGUNITID
INNER JOIN T_ORG_POSITIONMEMBER C
ON C.FPOSITIONID = A.FID
INNER JOIN T_BD_PERSON D
ON D.FID = C.FPERSONID
       LEFT JOIN t_pm_user u
       ON u.fpersonid = d.fid
WHERE D.FEMPLOYEETYPEID <> 'FTCmJT3SSRi1twufIaRb56KehbM='
AND B.FLONGNUMBER LIKE 'Z000001!Z000005!Z000041%'
   AND d.fdeletedstatus = 1