从json表插入，如何添加子查询以用json之外的值填充一列

6ss1mwsb 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(307)

我有table

Name           Null?    Type         
-------------- -------- ------------ 
PONO           NOT NULL NUMBER(5)    
REQUESTOR               VARCHAR2(50) 
COSTCENTER              VARCHAR2(5)  
ADDRESSSTREET           VARCHAR2(50) 
ADDRESSCITY             VARCHAR2(50) 
ADDRESSSTATE            VARCHAR2(2)  
ADDRESSZIP              VARCHAR2(10) 
ADDRESSCOUNTRY          VARCHAR2(50) 
AD_INFO                 VARCHAR2(50)

以及此类插入

INSERT INTO purchaseorder
      SELECT * FROM json_table(L_PO  FORMAT JSON, '$'
         COLUMNS (
           PONo            Number    PATH '$.PONumber',
           Requestor       VARCHAR2  PATH '$.Requestor',
           CostCenter      VARCHAR2  PATH '$.CostCenter',
           AddressStreet   VARCHAR2  PATH '$.Address.street',
           AddressCity     VARCHAR2  PATH '$.Address.city',
           AddressState    VARCHAR2  PATH '$.Address.state',
           AddressZip      VARCHAR2  PATH '$.Address.zipCode',
           AddressCountry  VARCHAR2  PATH '$.Address.country'));

我想从添加广告信息 select source from source_table where x=159 一次手术能做到这一点吗？

sql JSON oracle plsql

来源：https://stackoverflow.com/questions/62209591/insert-from-json-table-how-to-add-subquery-to-fill-one-column-with-value-outsid

1条答案

按热度按时间

7vux5j2d1#

如果您的表源\u表包含l\u po（json文件），那么您可以使用：

INSERT INTO purchaseorder
      SELECT sc.AD_INFO,jtbl.* FROM source_table sc,json_table(L_PO  FORMAT JSON, '$'
         COLUMNS (
           PONo            Number    PATH '$.PONumber',
           Requestor       VARCHAR2  PATH '$.Requestor',
           CostCenter      VARCHAR2  PATH '$.CostCenter',
           AddressStreet   VARCHAR2  PATH '$.Address.street',
           AddressCity     VARCHAR2  PATH '$.Address.city',
           AddressState    VARCHAR2  PATH '$.Address.state',
           AddressZip      VARCHAR2  PATH '$.Address.zipCode',
           AddressCountry  VARCHAR2  PATH '$.Address.country')) jtbl;

赞(0）回复(0）举报 2021-07-26

我来回答

从json表插入，如何添加子查询以用json之外的值填充一列

1条答案

相关问题

热门标签

最新问答