Updating tables through object views in oracle waydating
Hence I am trying to use a WITH clause during update but I'm hitting error in it.Can you advise the solution or any alternative way to do this: WITH x AS (SELECT rowid,case when count(distinct leg_division)over(partition by leg_distributor_vendor) = 1 THEN NULL ELSE sap_division end FROM sap_x_tt_agreement_ss1 WHERE dep_error_code IS NULL AND val_error_code IS NULL) UPDATE sap_x_tt_agreement_ss1 y,x SET sap_division = NULL WHERE x.rowid = y.rowid; select * from v$version; Error: ORA-00928: missing SELECT keyword Do let me know if test script is required.If you want more help with this, please provide the DDL for your tables (create table, constraints indexes) and an execution plan for the update. Asked: June 16, 2017 - am UTC Answered by: Connor Mc Donald - Last updated: June 22, 2017 - am UTC Category: SQL - Version: 184.108.40.206.0 Viewed 10K times! From last few days I came across many situations where I need to update a table based on some analytical function output.
February 26, 2016 - am UTC You may be able to write it as a merge or an updatable query - see Rajesh's comments below.An updatable materialized view lets you insert, update, and delete.You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.Using views encourages the use of Shared SQL with the benefit of reduced memory usage.The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
Search for updating tables through object views in oracle:
ROW_ID=1 ; This query returns uniqueid, md_Dt for every valid uniqueid. We tried using update with select but got the error 'cannot modify a column which maps to a non key-preserved table'.