Skip to Main Content
  • Questions

  • Updating Table Using with Clause or Analytical Function

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, amit.

Asked: June 16, 2017 - 3:49 am UTC

Last updated: June 22, 2017 - 5:33 am UTC

Version: 11.2.0.4.0

Viewed 50K+ times! This question is

You Asked

Hi,

Good Day !

From last few days I came across many situations where I need to update a table based on some analytical function output. Since, my table don't have any primary key I am having limitation on update using SQL and hence I left with the only alternatives of using PL/SQL. Since my table is too huge and update will be on many rows(some cases upto millions) I face severe bad performance.

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.

Regards,
Amit

and Connor said...

WITH frames a SELECT statement only. So here's an example of where you place it to be used within an update

SQL> create table t as select * from dba_objects ;

Table created.

SQL>
SQL> with x as
  2    ( select rowid r from t where owner = 'SCOTT' )
  3  update t
  4  set object_id = 100
  5  where rowid in ( select r from x );
update t
*
ERROR at line 3:
ORA-00928: missing SELECT keyword


SQL>
SQL> update t
  2  set object_id = 100
  3  where rowid in (
  4  with x as
  5    ( select rowid r from t where owner = 'SCOTT' )
  6  select r from x
  7  );

59 rows updated.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, but need some more help

amit chaudhary, June 20, 2017 - 4:40 am UTC

Hi,

I understood the usage of with clause now referring this case, but here my "Set" statement is using a constant value of 100.
My requirement is the set statement should use the value coming from the output of with clause or analytical function.

For example: This is my query:
SELECT OBJECT_NAME,OBJECT_TYPE,dense_Rank() over(partition by object_type order by object_name,created) New_Object_Id FROM t y where owner = 'SAPMIG'
order by 2,1,3;
I need to update the output of "New_object_id" from above query into my "t" table. So i tried below options but none of these seems working:

Option1: update t
set object_id = n_sorting_order
where rowid in (
with x as
( select rowid r,dense_Rank() over(partition by object_type order by object_name,created) n_sorting_order from t where owner = 'SCOTT' )
select r , n_sorting_order from x
);
/
Note : Option 1 having some syntax error, not sure how to fix it.
Option2: update t x
set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid);

Option3:
update t x
set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid)
where exists (SELECT 'x' FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid)
Connor McDonald
June 20, 2017 - 5:38 am UTC

Give us a full test case... and we'll give it our full attention :-)

Thanks, but need some more help

amit chaudhary, June 20, 2017 - 4:43 am UTC

Hi,

I understood the usage of with clause now referring this case, but here my "Set" statement is using a constant value of 100.
My requirement is the set statement should use the value coming from the output of with clause or analytical function.

For example: This is my query:
SELECT OBJECT_NAME,OBJECT_TYPE,dense_Rank() over(partition by object_type order by object_name,created) New_Object_Id FROM t y where owner = 'SAPMIG'
order by 2,1,3;
I need to update the output of "New_object_id" from above query into my "t" table. So i tried below options but none of these seems working:

Option1: update t
set object_id = n_sorting_order
where rowid in (
with x as
( select rowid r,dense_Rank() over(partition by object_type order by object_name,created) n_sorting_order from t where owner = 'SCOTT' )
select r , n_sorting_order from x
);
/
Note : Option 1 having some syntax error, not sure how to fix it.
Option2: update t x
set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid);

Option3:
update t x
set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid)
where exists (SELECT 'x' FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid)

Thanks, but need some more help

amit chaudhary, June 20, 2017 - 6:05 am UTC

Hi,

I understood the usage of with clause now referring this case, but here my "Set" statement is using a constant value of 100.
My requirement is the set statement should use the value coming from the output of with clause or analytical function.

For example: This is my query:
SELECT OBJECT_NAME,OBJECT_TYPE,dense_Rank() over(partition by object_type order by object_name,created) New_Object_Id FROM t y where owner = 'SAPMIG'
order by 2,1,3;
I need to update the output of "New_object_id" from above query into my "t" table. So i tried below options but none of these seems working:

Option1: update t
set object_id = n_sorting_order
where rowid in (
with x as
( select rowid r,dense_Rank() over(partition by object_type order by object_name,created) n_sorting_order from t where owner = 'SCOTT' )
select r , n_sorting_order from x
);
/
Note : Option 1 having some syntax error, not sure how to fix it.
Option2: update t x
set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid);

Option3:
update t x
set object_id = (SELECT dense_Rank() over(partition by object_type order by object_name,created) FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid)
where exists (SELECT 'x' FROM t y where owner = 'SAPMIG' and x.rowid = y.rowid)
Connor McDonald
June 22, 2017 - 5:33 am UTC

You could do something like

SQL> update t
  2  set object_name = (
  3     with t1 as (
  4         select /*+ materialize */ t.rowid rid,
  5                case when count(distinct trunc(created,'MM')) over ( partition by owner ) = 1 then 'X' else 'Y' end flag
  6         from t
  7         where object_type = 'TABLE'
  8    )
  9         select flag from t1
 10         where rid = t.rowid
 11  )
 12  where object_type = 'TABLE';

2841 rows updated.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d17p32g0zdt3w, child number 0
-------------------------------------
update t set object_name = (    with t1 as (        select /*+
materialize */ t.rowid rid,               case when count(distinct
trunc(created,'MM')) over ( partition by owner ) = 1 then 'X' else 'Y'
end flag        from t        where object_type = 'TABLE'   )
select flag from t1        where rid = t.rowid ) where object_type =
'TABLE'

Plan hash value: 890551451

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                          |                             |      1 |        |      0 |00:00:00.76 |    8879 |       |       |          |
|   1 |  UPDATE                                   | T                           |      1 |        |      0 |00:00:00.76 |    8879 |       |       |          |
|*  2 |   TABLE ACCESS FULL                       | T                           |      1 |   1630 |   2841 |00:00:00.01 |    1538 |       |       |          |
|   3 |   TEMP TABLE TRANSFORMATION               |                             |   2841 |        |   2841 |00:00:00.73 |    1538 |       |       |          |
|   4 |    LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D669D_9269CDB4 |      1 |        |      0 |00:00:00.01 |    1538 |  1024 |  1024 |          |
|   5 |     WINDOW SORT                           |                             |      1 |   1630 |   2841 |00:00:00.01 |    1538 |   232K|   232K|  206K (0)|
|*  6 |      TABLE ACCESS FULL                    | T                           |      1 |   1630 |   2841 |00:00:00.01 |    1538 |       |       |          |
|*  7 |    VIEW                                   |                             |   2841 |   1630 |   2841 |00:00:00.72 |       0 |       |       |          |
|   8 |     TABLE ACCESS FULL                     | SYS_TEMP_0FD9D669D_9269CDB4 |   2841 |   1630 |   8071K|00:00:00.36 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='TABLE')
   6 - filter("OBJECT_TYPE"='TABLE')
   7 - filter("RID"=:B1)


32 rows selected.

SQL>


so you can see the temp table was *loaded* just once (starts=1), but be aware we actually *probed* the temp table 2841 times (once per row). So if that temp result of yours is large, thats going to be very expensive.

So you might be better off loading your own temp table and using that as a source.


merge

Rajeshwaran, Jeyabal, June 22, 2017 - 11:30 am UTC

Any reason to ignore "MERGE" command, that seems to be big bulk operation that this row-by-row execution of UPDATE command.

demo@ORA11G> create table t as select * from all_objects;

Table created.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

demo@ORA11G> set serveroutput off
demo@ORA11G> update /*+ gather_plan_statistics */ t
  2    set object_name = (
  3       with t1 as (
  4           select /*+ materialize */ t.rowid rid,
  5                  case when count(distinct trunc(created,'MM')) over ( partition by owner ) = 1 then 'X' else 'Y' end flag
  6           from t
  7           where object_type = 'TABLE'
  8      )
  9           select flag from t1
 10           where rid = t.rowid
 11    )
 12  where object_type = 'TABLE';

212 rows updated.

demo@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dn2rr5aq9vjkq, child number 0
-------------------------------------
update /*+ gather_plan_statistics */ t   set object_name = (      with
t1 as (          select /*+ materialize */ t.rowid rid,
case when count(distinct trunc(created,'MM')) over ( partition by owner
) = 1 then 'X' else 'Y' end flag          from t          where
object_type = 'TABLE'     )          select flag from t1          where
rid = t.rowid   ) where object_type = 'TABLE'

Plan hash value: 890551451

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |                           |      1 |        |      0 |00:00:00.05 |    3046 |      1 |      1 |       |       |          |
|   1 |  UPDATE                     | T                         |      1 |        |      0 |00:00:00.05 |    3046 |      1 |      1 |       |       |          |
|*  2 |   TABLE ACCESS FULL         | T                         |      1 |   2525 |    212 |00:00:00.01 |    1089 |      0 |      0 |       |       |          |
|   3 |   TEMP TABLE TRANSFORMATION |                           |    212 |        |    212 |00:00:00.04 |    1523 |      1 |      1 |       |       |          |
|   4 |    LOAD AS SELECT           |                           |      1 |        |      0 |00:00:00.01 |    1092 |      0 |      1 |   266K|   266K|  266K (0)|
|   5 |     WINDOW SORT             |                           |      1 |   2525 |    212 |00:00:00.01 |    1089 |      0 |      0 | 27648 | 27648 |24576  (0)|
|*  6 |      TABLE ACCESS FULL      | T                         |      1 |   2525 |    212 |00:00:00.01 |    1089 |      0 |      0 |       |       |          |
|*  7 |    VIEW                     |                           |    212 |   2525 |    212 |00:00:00.02 |     427 |      1 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL       | SYS_TEMP_0FD9D6601_569A69 |    212 |   2525 |  44944 |00:00:00.03 |     427 |      1 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='TABLE')
   6 - filter("OBJECT_TYPE"='TABLE')
   7 - filter("RID"=:B1)


32 rows selected.

demo@ORA11G> rollback;

Rollback complete.

demo@ORA11G> merge /*+ gather_plan_statistics */ into t t1 using (
  2     select rowid rid ,
  3             case when count(distinct trunc(created,'mm')) over( partition by owner ) = 1 then 'X'
  4                                     else 'Y' end flag
  5     from t
  6     where object_type ='TABLE' ) t2
  7  on (t1.rowid = t2.rid)
  8  when matched then
  9     update set t1.object_name = t2.flag;

212 rows merged.

demo@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3st03j96unax0, child number 0
-------------------------------------
merge /*+ gather_plan_statistics */ into t t1 using (  select rowid rid
,   case when count(distinct trunc(created,'mm')) over( partition by
owner ) = 1 then 'X'      else 'Y' end flag  from t  where object_type
='TABLE' ) t2 on (t1.rowid = t2.rid) when matched then  update set
t1.object_name = t2.flag

Plan hash value: 2678347598

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |      1 |        |      0 |00:00:00.02 |    2398 |       |    |     |
|   1 |  MERGE                 | T    |      1 |        |      0 |00:00:00.02 |    2398 |       |    |     |
|   2 |   VIEW                 |      |      1 |        |    212 |00:00:00.01 |    2178 |       |    |     |
|*  3 |    HASH JOIN           |      |      1 |   2525 |    212 |00:00:00.01 |    2178 |  1519K|  1519K| 1484K (0)|
|   4 |     VIEW               |      |      1 |   2525 |    212 |00:00:00.01 |    1089 |       |    |     |
|   5 |      WINDOW SORT       |      |      1 |   2525 |    212 |00:00:00.01 |    1089 |    99K|    99K|          |
|*  6 |       TABLE ACCESS FULL| T    |      1 |   2525 |    212 |00:00:00.01 |    1089 |       |    |     |
|   7 |     TABLE ACCESS FULL  | T    |      1 |  75747 |  75747 |00:00:00.01 |    1089 |       |    |     |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1".ROWID="T2"."RID")
   6 - filter("OBJECT_TYPE"='TABLE')


29 rows selected.

demo@ORA11G>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.