• 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);

  • 相关阅读:
    关于缓存技术
    很好的C程序
    静态变量和非静态变量
    关于系统
    数据存储
    access判断查询的结果是否为空,等同于SQL ISNULL()
    Access数据库常用函数大全
    MM 常用表
    SAP AFS BAPI 不允许业务对象 BUS2032 和销售凭证类别 H 的组合
    ABAP 语言特色
  • 原文地址:https://www.cnblogs.com/dozn/p/9008708.html
Copyright © 2020-2023  润新知