Oracle-两表关联更新和插入

需求：

表a(com_name,stock_code,com_sacle,mark,market_location,company_name)

表b(com_name,stock_code,com_sacle)

如果a.stock_code=b.stock_code 把b.com_name,b.com_scale 插入a.com_name,a.com_scale

如果表b.stock_code 在表a中没有 则把表b(com_name,stock_code,com_sacle)插入表a

 

过程：

--根据tmp_stock表的字段补全stock_collection的字段
update (select a.com_name a1,a.com_scale a2,b.com_name b1,b.com_scale b2
from stock_collection a,tmp_stock b where a.stock_code=b.stock_code)
set a1=b1,a2=b2;

-----------------出现错误-----------------------

错误报告 -
SQL 错误: ORA-01779: 无法修改与非键值保存表对应的列
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.

------解决办法：设置唯一约束

alter table tmp_stock modify stock_code unique;

----------------------------------------------------

--插入tmp_stock表中有，而stock_collection表中没有的数据
insert into stock_collection(stock_code,com_name,com_scale)
select stock_code,com_name,com_scale from tmp_stock b 
where b.stock_code not in (select stock_code from stock_collection a);

 

转载于:https://www.cnblogs.com/dozn/p/9008708.html