欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle数据库多表联合修改的三种语句方法介绍

程序员文章站 2022-05-28 20:14:45
1、效率最快的 MERGE INTO user_member_info a USING ( select min(UP.created) as cre,min(up.shop_...

1、效率最快的

MERGE INTO user_member_info a

USING ( select min(UP.created) as cre,min(up.shop_no) as shop_no,up.memberid as memberid

from user_platform_relation up,

( select memberid as mid,min(created) as cre

from user_platform_relation

group by memberid) um

where up.created = um.cre and up.memberid = um.mid

group by up.memberid) b

ON (a.id = b.memberid

)

WHEN MATCHED THEN UPDATE SET a.created = b.cre,a.shop_no = b.shop_no,a.modified_time = sysdate

where a.shop_no is null;

2、效率其次

update (select ui.userid as uiuid,oi.user_id as oiuid,

ui.id as uimid,oi.memberid as oimid,

oi.modified_time as oitime

from user_member_info ui, reserveorder_info oi

where ui.brandid = oi.brandid and ui.mobile = oi.telphone)

set oiuid = uiuid, oimid = uimid;

3、效率最差

UPDATE RETURN_FACTORYORDER r SET (r.XSBILLDATE,r.DQ) = (

SELECT f.saletime,f.bezei FROM RF f WHERE r.BILL = f.BILL

)