需求:
表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);