假设表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