如何update字段

Harryjing2018 2019-12-23 05:18:50
SELECT * FROM IFSAPP.PURCHASE_ORDER 
WHERE ORDER_NO='1922653'




在IFSAPP.PURCHASE_ORDER这个视图里,订单号是1922653,有一个APPROVE_RULE的字段是是802,我想改在804,怎么改?谢谢
...全文
106 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
lhdz_bj 2019-12-25
  • 打赏
  • 举报
回复
引用 6 楼 Harryjing2018 的回复:
[quote=引用 1 楼 魔法师的荣耀 的回复:]
视图是要改不了的,要在原表里面改。
CREATE OR REPLACE VIEW IFSAPP.PURCHASE_ORDER AS
SELECT order_no order_no,
addr_no addr_no,
authorize_code authorize_code,
authorize_id authorize_id,
blanket_order blanket_order,
buyer_code buyer_code,
contract contract,
currency_code currency_code,
delivery_address delivery_address,
delivery_terms delivery_terms,
language_code language_code,
note_id note_id,
pre_accounting_id pre_accounting_id,
ship_via_code ship_via_code,
vendor_no vendor_no,
change_date change_date,
contact contact,
date_entered date_entered,
delivery_terms_desc delivery_terms_desc,
label_note label_note,
note_text note_text,
order_code order_code,
order_date order_date,
substrb(Pick_List_Flag_API.Decode(pick_list_flag),1,200) pick_list_flag,
pick_list_flag pick_list_flag_db,
substrb(Printed_Flag_API.Decode(printed_flag),1,200) printed_flag,
printed_flag printed_flag_db,
revision revision,
ship_via_desc ship_via_desc,
vendor_co_no vendor_co_no,
wanted_receipt_date wanted_receipt_date,
recipient_name recipient_name,
country_code country_code,
country_code country_code_db,
customer_po_no customer_po_no,
substr(Site_Api.Get_Company(contract),1,20) company,
pay_term_id pay_term_id,
substrb(Order_Sent_API.Decode(order_sent),1,200) order_sent,
order_sent order_sent_db,
substrb(Change_Request_Sent_API.Decode(change_request_sent),1,200) change_request_sent,
change_request_sent change_request_sent_db,
approval_rule approval_rule,
forwarder_id forwarder_id,
substrb(Blanket_Date_API.Decode(blanket_date),1,200) blanket_date,
blanket_date blanket_date_db,
substrb(Schedule_Agreement_Order_API.Decode(schedule_agreement_order),1,200) schedule_agreement_order,
schedule_agreement_order schedule_agreement_order_db,
internal_destination internal_destination,
destination_id destination_id,
address1 address1,
address2 address2,
zip_code zip_code,
city city,
addr_state addr_state,
--Shirley 20030516
send_date send_date,
--begin: PPJY.SD.006,Henry
multi_contract multi_contract,
multi_order_id multi_order_id,
multi_supply_code multi_supply_code_db,
substrb(Order_Supply_Type_API.Decode(multi_supply_code),1,200) multi_supply_code,
multi_customer_no multi_customer_no,
def_location_no def_location_no,
Return_Location_No Return_Location_No,
Return_Reason Return_Reason,
--end: PPJY.SD.006
rowid objid,
ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000)) objversion,
rowstate objstate,
substrb(PURCHASE_ORDER_API.Finite_State_Events__(rowstate),1,253) objevents,
substrb(PURCHASE_ORDER_API.Finite_State_Decode__(rowstate),1,253) state
FROM purchase_order_tab
WITH read only;

我只截取了一部份,我都不确定我这是不是原表了,purchase_order_tab不知道是不是原表[/quote]

1、怎么获取视图定义,你知道了,我就不回答了;
2、你这个算不上复杂视图,应该可以update,但后面有个read only,可能会不让改,你可以试试。
update IFSAPP.PURCHASE_ORDER set APPROVE_RULE=804 where APPROVE_RULE=802;
Harryjing2018 2019-12-25
  • 打赏
  • 举报
回复
引用 1 楼 魔法师的荣耀 的回复:
视图是要改不了的,要在原表里面改。
CREATE OR REPLACE VIEW IFSAPP.PURCHASE_ORDER AS
SELECT order_no                       order_no,
       addr_no                        addr_no,
       authorize_code                 authorize_code,
       authorize_id                   authorize_id,
       blanket_order                  blanket_order,
       buyer_code                     buyer_code,
       contract                       contract,
       currency_code                  currency_code,
       delivery_address               delivery_address,
       delivery_terms                 delivery_terms,
       language_code                  language_code,
       note_id                        note_id,
       pre_accounting_id              pre_accounting_id,
       ship_via_code                  ship_via_code,
       vendor_no                      vendor_no,
       change_date                    change_date,
       contact                        contact,
       date_entered                   date_entered,
       delivery_terms_desc            delivery_terms_desc,
       label_note                     label_note,
       note_text                      note_text,
       order_code                     order_code,
       order_date                     order_date,
       substrb(Pick_List_Flag_API.Decode(pick_list_flag),1,200) pick_list_flag,
       pick_list_flag                 pick_list_flag_db,
       substrb(Printed_Flag_API.Decode(printed_flag),1,200) printed_flag,
       printed_flag                   printed_flag_db,
       revision                       revision,
       ship_via_desc                  ship_via_desc,
       vendor_co_no                   vendor_co_no,
       wanted_receipt_date            wanted_receipt_date,
       recipient_name                 recipient_name,
       country_code                   country_code,
       country_code                   country_code_db,
       customer_po_no                 customer_po_no,
       substr(Site_Api.Get_Company(contract),1,20) company,
       pay_term_id                    pay_term_id,
       substrb(Order_Sent_API.Decode(order_sent),1,200) order_sent,
       order_sent                     order_sent_db,
       substrb(Change_Request_Sent_API.Decode(change_request_sent),1,200) change_request_sent,
       change_request_sent            change_request_sent_db,
       approval_rule                  approval_rule,
       forwarder_id                   forwarder_id,
       substrb(Blanket_Date_API.Decode(blanket_date),1,200) blanket_date,
       blanket_date                   blanket_date_db,
       substrb(Schedule_Agreement_Order_API.Decode(schedule_agreement_order),1,200) schedule_agreement_order,
       schedule_agreement_order       schedule_agreement_order_db,
       internal_destination           internal_destination,
       destination_id                 destination_id,
       address1                       address1,
       address2                       address2,
       zip_code                       zip_code,
       city                           city,
       addr_state                     addr_state,
       --Shirley    20030516
       send_date                      send_date,
		 --begin: PPJY.SD.006,Henry
		 multi_contract					  multi_contract,
		 multi_order_id					  multi_order_id,
		 multi_supply_code				  multi_supply_code_db,
		 substrb(Order_Supply_Type_API.Decode(multi_supply_code),1,200) multi_supply_code,
		 multi_customer_no				  multi_customer_no,
		 def_location_no					  def_location_no,
		 Return_Location_No				  Return_Location_No,
		 Return_Reason						  Return_Reason,
		 --end: PPJY.SD.006
       rowid                         objid,
       ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000))                    objversion,
       rowstate                      objstate,
       substrb(PURCHASE_ORDER_API.Finite_State_Events__(rowstate),1,253)                     objevents,
       substrb(PURCHASE_ORDER_API.Finite_State_Decode__(rowstate),1,253)                         state
FROM   purchase_order_tab
WITH   read only;
我只截取了一部份,我都不确定我这是不是原表了,purchase_order_tab不知道是不是原表
Harryjing2018 2019-12-25
  • 打赏
  • 举报
回复
引用 3 楼 lhdz_bj 的回复:
[quote=引用 楼主 Harryjing2018 的回复:]
SELECT * FROM IFSAPP.PURCHASE_ORDER 
WHERE ORDER_NO='1922653'
在IFSAPP.PURCHASE_ORDER这个视图里,订单号是1922653,有一个APPROVE_RULE的字段是是802,我想改在804,怎么改?谢谢
关键看你视图怎么定义的,复杂视图有时不支持update操作。[/quote] 我怎么看到视图的定义?谢谢。
Harryjing2018 2019-12-25
  • 打赏
  • 举报
回复
引用 1 楼 魔法师的荣耀 的回复:
视图是要改不了的,要在原表里面改。
我怎么样找到对应的原表呢?
Harryjing2018 2019-12-25
  • 打赏
  • 举报
回复
方法如下: https://blog.csdn.net/beyond911/article/details/103698413
Harryjing2018 2019-12-25
  • 打赏
  • 举报
回复
引用 7 楼 lhdz_bj 的回复:
[quote=引用 6 楼 Harryjing2018 的回复:] [quote=引用 1 楼 魔法师的荣耀 的回复:] 视图是要改不了的,要在原表里面改。
CREATE OR REPLACE VIEW IFSAPP.PURCHASE_ORDER AS
SELECT order_no                       order_no,
       addr_no                        addr_no,
       authorize_code                 authorize_code,
       authorize_id                   authorize_id,
       blanket_order                  blanket_order,
       buyer_code                     buyer_code,
       contract                       contract,
       currency_code                  currency_code,
       delivery_address               delivery_address,
       delivery_terms                 delivery_terms,
       language_code                  language_code,
       note_id                        note_id,
       pre_accounting_id              pre_accounting_id,
       ship_via_code                  ship_via_code,
       vendor_no                      vendor_no,
       change_date                    change_date,
       contact                        contact,
       date_entered                   date_entered,
       delivery_terms_desc            delivery_terms_desc,
       label_note                     label_note,
       note_text                      note_text,
       order_code                     order_code,
       order_date                     order_date,
       substrb(Pick_List_Flag_API.Decode(pick_list_flag),1,200) pick_list_flag,
       pick_list_flag                 pick_list_flag_db,
       substrb(Printed_Flag_API.Decode(printed_flag),1,200) printed_flag,
       printed_flag                   printed_flag_db,
       revision                       revision,
       ship_via_desc                  ship_via_desc,
       vendor_co_no                   vendor_co_no,
       wanted_receipt_date            wanted_receipt_date,
       recipient_name                 recipient_name,
       country_code                   country_code,
       country_code                   country_code_db,
       customer_po_no                 customer_po_no,
       substr(Site_Api.Get_Company(contract),1,20) company,
       pay_term_id                    pay_term_id,
       substrb(Order_Sent_API.Decode(order_sent),1,200) order_sent,
       order_sent                     order_sent_db,
       substrb(Change_Request_Sent_API.Decode(change_request_sent),1,200) change_request_sent,
       change_request_sent            change_request_sent_db,
       approval_rule                  approval_rule,
       forwarder_id                   forwarder_id,
       substrb(Blanket_Date_API.Decode(blanket_date),1,200) blanket_date,
       blanket_date                   blanket_date_db,
       substrb(Schedule_Agreement_Order_API.Decode(schedule_agreement_order),1,200) schedule_agreement_order,
       schedule_agreement_order       schedule_agreement_order_db,
       internal_destination           internal_destination,
       destination_id                 destination_id,
       address1                       address1,
       address2                       address2,
       zip_code                       zip_code,
       city                           city,
       addr_state                     addr_state,
       --Shirley    20030516
       send_date                      send_date,
		 --begin: PPJY.SD.006,Henry
		 multi_contract					  multi_contract,
		 multi_order_id					  multi_order_id,
		 multi_supply_code				  multi_supply_code_db,
		 substrb(Order_Supply_Type_API.Decode(multi_supply_code),1,200) multi_supply_code,
		 multi_customer_no				  multi_customer_no,
		 def_location_no					  def_location_no,
		 Return_Location_No				  Return_Location_No,
		 Return_Reason						  Return_Reason,
		 --end: PPJY.SD.006
       rowid                         objid,
       ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000))                    objversion,
       rowstate                      objstate,
       substrb(PURCHASE_ORDER_API.Finite_State_Events__(rowstate),1,253)                     objevents,
       substrb(PURCHASE_ORDER_API.Finite_State_Decode__(rowstate),1,253)                         state
FROM   purchase_order_tab
WITH   read only;
我只截取了一部份,我都不确定我这是不是原表了,purchase_order_tab不知道是不是原表[/quote] 1、怎么获取视图定义,你知道了,我就不回答了; 2、你这个算不上复杂视图,应该可以update,但后面有个read only,可能会不让改,你可以试试。 update IFSAPP.PURCHASE_ORDER set APPROVE_RULE=804 where APPROVE_RULE=802;[/quote]
引用 7 楼 lhdz_bj 的回复:
[quote=引用 6 楼 Harryjing2018 的回复:] [quote=引用 1 楼 魔法师的荣耀 的回复:] 视图是要改不了的,要在原表里面改。
CREATE OR REPLACE VIEW IFSAPP.PURCHASE_ORDER AS
SELECT order_no                       order_no,
       addr_no                        addr_no,
       authorize_code                 authorize_code,
       authorize_id                   authorize_id,
       blanket_order                  blanket_order,
       buyer_code                     buyer_code,
       contract                       contract,
       currency_code                  currency_code,
       delivery_address               delivery_address,
       delivery_terms                 delivery_terms,
       language_code                  language_code,
       note_id                        note_id,
       pre_accounting_id              pre_accounting_id,
       ship_via_code                  ship_via_code,
       vendor_no                      vendor_no,
       change_date                    change_date,
       contact                        contact,
       date_entered                   date_entered,
       delivery_terms_desc            delivery_terms_desc,
       label_note                     label_note,
       note_text                      note_text,
       order_code                     order_code,
       order_date                     order_date,
       substrb(Pick_List_Flag_API.Decode(pick_list_flag),1,200) pick_list_flag,
       pick_list_flag                 pick_list_flag_db,
       substrb(Printed_Flag_API.Decode(printed_flag),1,200) printed_flag,
       printed_flag                   printed_flag_db,
       revision                       revision,
       ship_via_desc                  ship_via_desc,
       vendor_co_no                   vendor_co_no,
       wanted_receipt_date            wanted_receipt_date,
       recipient_name                 recipient_name,
       country_code                   country_code,
       country_code                   country_code_db,
       customer_po_no                 customer_po_no,
       substr(Site_Api.Get_Company(contract),1,20) company,
       pay_term_id                    pay_term_id,
       substrb(Order_Sent_API.Decode(order_sent),1,200) order_sent,
       order_sent                     order_sent_db,
       substrb(Change_Request_Sent_API.Decode(change_request_sent),1,200) change_request_sent,
       change_request_sent            change_request_sent_db,
       approval_rule                  approval_rule,
       forwarder_id                   forwarder_id,
       substrb(Blanket_Date_API.Decode(blanket_date),1,200) blanket_date,
       blanket_date                   blanket_date_db,
       substrb(Schedule_Agreement_Order_API.Decode(schedule_agreement_order),1,200) schedule_agreement_order,
       schedule_agreement_order       schedule_agreement_order_db,
       internal_destination           internal_destination,
       destination_id                 destination_id,
       address1                       address1,
       address2                       address2,
       zip_code                       zip_code,
       city                           city,
       addr_state                     addr_state,
       --Shirley    20030516
       send_date                      send_date,
		 --begin: PPJY.SD.006,Henry
		 multi_contract					  multi_contract,
		 multi_order_id					  multi_order_id,
		 multi_supply_code				  multi_supply_code_db,
		 substrb(Order_Supply_Type_API.Decode(multi_supply_code),1,200) multi_supply_code,
		 multi_customer_no				  multi_customer_no,
		 def_location_no					  def_location_no,
		 Return_Location_No				  Return_Location_No,
		 Return_Reason						  Return_Reason,
		 --end: PPJY.SD.006
       rowid                         objid,
       ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000))                    objversion,
       rowstate                      objstate,
       substrb(PURCHASE_ORDER_API.Finite_State_Events__(rowstate),1,253)                     objevents,
       substrb(PURCHASE_ORDER_API.Finite_State_Decode__(rowstate),1,253)                         state
FROM   purchase_order_tab
WITH   read only;
我只截取了一部份,我都不确定我这是不是原表了,purchase_order_tab不知道是不是原表[/quote] 1、怎么获取视图定义,你知道了,我就不回答了; 2、你这个算不上复杂视图,应该可以update,但后面有个read only,可能会不让改,你可以试试。 update IFSAPP.PURCHASE_ORDER set APPROVE_RULE=804 where APPROVE_RULE=802;[/quote]

SELECT * FROM IFSAPP.PURCHASE_ORDER_TAB
WHERE ORDER_NO='1922653'
FOR UPDATE 
IFSAPP.PURCHASE_ORDER_TAB SET APPROVEAL_RULE='804' WHERE ORDER_NO='1922653'
  
我想通过脚本修改,错在哪了呢?谢谢
stelf 2019-12-24
  • 打赏
  • 举报
回复
要更新视图好像有很多限制条件,比如有主键关联,数据不能发散等。最好的办法就是找到对应的表及表相关的过滤条件,然后更新表字段
魔法师的荣耀 2019-12-24
  • 打赏
  • 举报
回复
视图是要改不了的,要在原表里面改。
lhdz_bj 2019-12-24
  • 打赏
  • 举报
回复
引用 楼主 Harryjing2018 的回复:
SELECT * FROM IFSAPP.PURCHASE_ORDER 
WHERE ORDER_NO='1922653'




在IFSAPP.PURCHASE_ORDER这个视图里,订单号是1922653,有一个APPROVE_RULE的字段是是802,我想改在804,怎么改?谢谢


关键看你视图怎么定义的,复杂视图有时不支持update操作。

17,377

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧