oracle使用一个表的数据更新另一张表

--查询库位表中没有的库位
select * from L_IMPORTDATA_TEMP  where location_code not in (select t.location_code from l_location t) ;

--第一步:插入库位表中没有的刻录
  --insert into l_location select * from L_IMPORTDATA_TEMP  where location_code not in (select t.location_code from l_location t) ;
  --commit;
--第二步:更新记录数据
--没用select * from l_location l where l.location_code in (select location_code from L_IMPORTDATA_TEMP);
--闪回Insert Into L_LOCATION (Select * From L_LOCATION As Of Timestamp to_Timestamp('2012-3-24  13:24:30','yyyy-mm-dd hh24:mi:ss') )

savepoint derek;


UPDATE  l_location L
SET  ( materialscode,capacity,inventory,shelfnum,orders,status,fathercode,isvirtualreal)=
(SELECT materialscode,capacity,inventory,shelfnum,orders,status,fathercode,isvirtualreal FROM L_IMPORTDATA_TEMP T WHERE l.location_code = t.location_code and  l.storagecode=209)
where location_code in (select location_code from L_IMPORTDATA_TEMP) and l.storagecode=209;
 

 

commit;

阅读更多
文章标签: oracle insert
上一篇oracle误删数据之表闪回
下一篇php连接oracle设定字符集,避免乱码
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭