我要做的是从一个表导入到另一个结构相同的表,如果重复插入,不重复,更新,这样写的命令更新速度太慢,请大家帮忙看看!
UPDATE MAIN_PAS X SET AMOUNT=X.AMOUNT+
(SELECT SUM(AMOUNT) AMOUNT FROM temp_import b WHERE b.VEHICLECD=x.VEHICLECD and b.MODELCD=x.MODELCD and b.BRANDCD=x.BRANDCD
and b.DISBRANDCD=x.DISBRANDCD and b.MAKERCD=x.MAKERCD and b.COLORCD=x.COLORCD and b.DISMAKERCD=x.DISMAKERCD and b.GROUPCD=x.GROUPCD and b.OLDSEGMENT=x.OLDSEGMENT and b.CLASS1CD=x.CLASS1CD
and b.CLASS2CD=x.CLASS2CD and b.CLASS3=x.CLASS3CD and b.BODYTYPE=x.BODYTYPE and b.DOMECBUCD=x.DOMECBUCD and b.PROVINCECD=x.PROVINCECD and b.CITYCD=x.CITYCD and
b.DISTRICTCD =x.DISTRICTCD and b.POSTALCODE=x.POSTALCODE and b.REGYEARMONTH=x.REGYEARMONTH and b.USAGECD=x.USAGECD and b.ACQUISITIONCD=x.ACQUISITIONCD and b. VIN =x. VIN and b.OWNERSHIPCD=x.OWNERSHIPCD
and b.COUNTRYCD=x.COUNTRYCD and b.DISCOUNTRYCD and b.ENGINE=x.ENGINE and b.FUELCD=x.FUELCD and b.DISPLACEMENT=x.DISPLACEMENT and b.TRANSMISSIONCD=x.TRANSMISSIONCD and b.GEARCD=x.GEARCD and b. PRICE=x.PRICE
and b.OWNERYEAR=x.OWNERYEAR
UPDATE MAIN_PAS X SET AMOUNT=X.AMOUNT+
(SELECT SUM(AMOUNT) AMOUNT FROM temp_import b WHERE b.VEHICLECD=x.VEHICLECD and b.MODELCD=x.MODELCD and b.BRANDCD=x.BRANDCD
and b.DISBRANDCD=x.DISBRANDCD and b.MAKERCD=x.MAKERCD and b.COLORCD=x.COLORCD and b.DISMAKERCD=x.DISMAKERCD and b.GROUPCD=x.GROUPCD and b.OLDSEGMENT=x.OLDSEGMENT and b.CLASS1CD=x.CLASS1CD
and b.CLASS2CD=x.CLASS2CD and b.CLASS3=x.CLASS3CD and b.BODYTYPE=x.BODYTYPE and b.DOMECBUCD=x.DOMECBUCD and b.PROVINCECD=x.PROVINCECD and b.CITYCD=x.CITYCD and
b.DISTRICTCD =x.DISTRICTCD and b.POSTALCODE=x.POSTALCODE and b.REGYEARMONTH=x.REGYEARMONTH and b.USAGECD=x.USAGECD and b.ACQUISITIONCD=x.ACQUISITIONCD and b. VIN =x. VIN and b.OWNERSHIPCD=x.OWNERSHIPCD
and b.COUNTRYCD=x.COUNTRYCD and b.DISCOUNTRYCD and b.ENGINE=x.ENGINE and b.FUELCD=x.FUELCD and b.DISPLACEMENT=x.DISPLACEMENT and b.TRANSMISSIONCD=x.TRANSMISSIONCD and b.GEARCD=x.GEARCD and b. PRICE=x.PRICE
and b.OWNERYEAR=x.OWNERYEAR
解决方案 »
- 求 数据库大神指教
- sqlldr导致索引失效,重建花销太大,咋整?各路兄弟高人能不能支个招?
- ORA-00972: Identification too long 这个问题困扰我好久,希望高手给予执教,谢谢。
- oracle的ISQ*PLUS的HTTP端口号怎么把它查出来,还有EM的端口号?
- 启动oracle 10g,出现 到实例的代理连接 失败
- 我在查找数据库对象中找不着创建成功的表,但是TableSpace Map里却能看到,但不能修改属性.
- 在视图中可以使用if控制语句吗,如果能如何使用?
- 如何将oracle表中的varchar2类型字段转换成clob型字段
- 错在哪里呀
- 在win2000server上安装oracle8.1.7,按步骤卸载后,却无法安装,求解
- 请问,编写一个PL/SQL程序时,是不是要将Exception语句块放到程序的最后?
- oracle行转列(急,在线等,分不够可以再加)
USING (SELECT .........,SUM(AMOUNT) AMOUNT FROM temp_import GROUP BY .....) B
ON (b.VEHICLECD=x.VEHICLECD AND ... AND ...)
WHEN MATCHED THEN
UPDATE SET X.AMOUNT=X.AMOUNT+B.AMOUNT
WHEN NOT MATCHED THEN
INSERT (X.,X.,...) VALUES(B.,B.,...)
/
-- Local variables here
i integer;
result varchar2(10);
begin
-- Test statements here
begin
insert into aud_type(app_code,aud_type,aud_item_code,aud_item_name) values('1','1','1','a');
result := 'OK';
exception
when DUP_VAL_ON_INDEX then
begin
update aud_type set aud_item_name = 'a' where app_code ='1' and aud_type='1' and aud_item_code='1';
result := 'OK';
exception
when others then
result := 'ERR';
end;
when others then
result := 'ERR';
end;
end;把这个改成Procedure
(
select * from aud_type
minus
select * from MAIN_PAS
);
这样能行吗? 看热闹的.