Home » SQL & PL/SQL » SQL & PL/SQL » Find row count after update (Oracle 12c, Linux)
Find row count after update [message #686677] Fri, 18 November 2022 15:37 Go to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Hi,

I have to update 500 rows manually like this (added only 5 here ). Is there a better way to find row count rather than incrementing after each update? I have to keep this "tot := tot + sql%rowcount;" 500 times to get the final row count?

Is there a better way to find row count?

CREATE TABLE a

   (       SNO VARCHAR2(9 BYTE)

   ) ;

  

Insert into A (SNO) values ('000250788');

Insert into A (SNO) values ('000280330');

Insert into A (SNO) values ('000280758');

Insert into A (SNO) values ('000220842');

Insert into A (SNO) values ('000353719');

 

commit;

 

 

declare

tot number := 0;

begin

Update a set sno = '100000227' where sno = '000220842';

tot := tot + sql%rowcount;

Update a set sno = '002934741' where sno = '000250788';

tot := tot + sql%rowcount;

Update a set sno = '000020043' where sno = '000280330';

tot := tot + sql%rowcount;

Update a set sno = '000020130' where sno = '000280758';

tot := tot + sql%rowcount;

Update a set sno = '008087360' where sno = '000353719';

tot := tot + sql%rowcount;

dbms_output.put_line('Rows Updated = ' || tot);

End;
Re: Find row count after update [message #686678 is a reply to message #686677] Sat, 19 November 2022 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about creating a procedure you'd then call and perform updates? Put it into a package whose variable will track number of changes. Something like this:

SQL> create or replace package pkg_sno as
  2    g_total number := 0;
  3    procedure p_update (par_old_sno in number, par_new_sno in number);
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg_sno as
  2    procedure p_update (par_old_sno in number, par_new_sno in number) is
  3    begin
  4      update a set sno = par_new_sno where sno = par_Old_sno;
  5      g_total := g_total + sql%rowcount;
  6    end;
  7  end;
  8  /

Package body created.

SQL> set serveroutput on
SQL> begin
  2    pkg_sno.p_update ('000220842', '100000227');
  3    pkg_sno.p_update ('000250788', '002934741');
  4    pkg_sno.p_update ('000280330', '000020043');
  5
  6    dbms_output.put_line('Updated ' || pkg_sno.g_total || ' row(s)');
  7  end;
  8  /
Updated 3 row(s)

PL/SQL procedure successfully completed.

SQL>


On the other hand, consider creating a table which would contain two columns: old and new SNO values; then simply update the table in one run, e.g.

SQL> create table sno_pairs (old_sno varchar2(9), new_sno varchar2(9));

Table created.

SQL> insert into sno_pairs (old_sno, new_sno)
  2    select '000220842', '100000227' from dual union all
  3    select '000250788', '002934741' from dual union all
  4    select '000280330', '000020043' from dual;

3 rows created.

SQL> update a set
  2    a.sno = (select b.new_sno
  3             from sno_pairs b
  4             where b.old_sno = a.sno
  5            )
  6  where exists (select null
  7                from sno_pairs c
  8                where c.old_sno = a.sno
  9               );

3 rows updated.

SQL>

If you already have these (old, new) SNO pairs somewhere (such as an Excel table), you can easily import them into the table instead of INSERTing them one-by-one and simplify the process.
Re: Find row count after update [message #686680 is a reply to message #686677] Sat, 19 November 2022 06:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
I'll assume you aren't updating same row multiple times. If so, use MERGE:

begin
    merge
      into a t
      using(
            with driver as (
                            select '100000227' new_sno,'000220842' sno from dual union all
                            select '002934741','000250788' from dual union all
                            select '000020043','000280330' from dual union all
                            select '000020130','000280758' from dual union all
                            select '008087360','000353719' from dual
                           )
            select  a.rowid rid,
                    d.new_sno
              from  a,
                    driver d
              where a.sno = d.sno
           ) s
      on (
          t.rowid = s.rid
         )
      when matched
        then
          update
             set t.sno = s.new_sno;
    dbms_output.put_line('Rows Updated = ' || sql%rowcount);
end;
/
Rows Updated = 5

PL/SQL procedure successfully completed.

SQL>
SY.
Re: Find row count after update [message #686681 is a reply to message #686678] Sat, 19 November 2022 07:21 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
This is a good one, but issue is since its production system they wont allow me to create packages.
Re: Find row count after update [message #686682 is a reply to message #686680] Sat, 19 November 2022 07:24 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Solomon.

Actually I was halfway through this and u did it completely for me. Since I dont have any columns to identify which rows got updated, is there a workaround to find which rows got updated. Since it was an old system , they dont have id/date as to who/when updated it.

We can find by updated id's but what if we get new id's into production with the same id's...which did ..last week ..

Is there a way to find which rows got updated by this statement?

Thanks.
Re: Find row count after update [message #686684 is a reply to message #686682] Sat, 19 November 2022 20:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Unfortunately MERGE doesn't support RETURNING clause. You will need to use UPDATE. I'll take example Littlefoot posted:

declare
    type rowid_tbl_type
      is table of rowid;
    v_updated_rowid_list rowid_tbl_type;
begin
    update a
       set a.sno = (
                    select  b.new_sno
                      from  sno_pairs b
                      where b.old_sno = a.sno
                   )
     where exists (
                   select  null
                     from  sno_pairs c
                     where c.old_sno = a.sno
                  )
     returning rowid bulk collect into v_updated_rowid_list;
    for v_i in 1..v_updated_rowid_list.count loop
      dbms_output.put_line('updated rowid ' || v_updated_rowid_list(v_i));
    end loop;
end;
/
updated rowid AAApZRAALAAAHoUAAD
updated rowid AAApZRAALAAAHoUAAA
updated rowid AAApZRAALAAAHoUAAB
updated rowid AAApZRAALAAAHoUAAC
updated rowid AAApZRAALAAAHoUAAE

PL/SQL procedure successfully completed.

SQL>
SY.

[Updated on: Sat, 19 November 2022 20:08]

Report message to a moderator

Re: Find row count after update [message #686685 is a reply to message #686684] Sun, 20 November 2022 06:21 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Thanks Solomon.

Only Q is we are not allowed to create tables in Production.

How can we run the code without using this table sno_pairs?

Thanks much.
Re: Find row count after update [message #686686 is a reply to message #686685] Sun, 20 November 2022 11:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You could:

declare
    type rowid_tbl_type
      is table of rowid;
    v_updated_rowid_list rowid_tbl_type;
begin
    update a
       set a.sno = (
                   with driver as (
                                   select '100000227' new_sno,'000220842' sno from dual union all
                                   select '002934741','000250788' from dual union all
                                   select '000020043','000280330' from dual union all
                                   select '000020130','000280758' from dual union all
                                   select '008087360','000353719' from dual
                                  )
                    select  d.new_sno
                      from  driver d
                      where d.sno = a.sno
                   )
     where exists (
                   with driver as (
                                   select '100000227' new_sno,'000220842' sno from dual union all
                                   select '002934741','000250788' from dual union all
                                   select '000020043','000280330' from dual union all
                                   select '000020130','000280758' from dual union all
                                   select '008087360','000353719' from dual
                                  )
                   select  null
                     from  driver d
                     where d.sno = a.sno
                  )
     returning rowid bulk collect into v_updated_rowid_list;
    for v_i in 1..v_updated_rowid_list.count loop
      dbms_output.put_line('updated rowid ' || v_updated_rowid_list(v_i));
    end loop;
end;
/
updated rowid AAApZRAALAAAHoUAAD
updated rowid AAApZRAALAAAHoUAAA
updated rowid AAApZRAALAAAHoUAAB
updated rowid AAApZRAALAAAHoUAAC
updated rowid AAApZRAALAAAHoUAAE

PL/SQL procedure successfully completed.

SQL>
But I completely forgot about FORAll (it might be a bit less efficient but less writing):

declare
    type rowid_tbl_type
      is table of rowid;
    v_updated_rowid_list rowid_tbl_type;
    v_sno_list sys.OdciVarchar2List;
    v_new_sno_list sys.OdciVarchar2List;
begin
    v_sno_list := sys.OdciVarchar2List(
                                       '000220842',
                                       '000250788',
                                       '000280330',
                                       '000280758',
                                       '000353719'
                                       );
    v_new_sno_list := sys.OdciVarchar2List(
                                           '100000227',
                                           '002934741',
                                           '000020043',
                                           '000020130',
                                           '008087360'
                                          );
    forall v_i in 1..v_sno_list.count
      update a
         set a.sno = v_new_sno_list(v_i)
       where a.sno = v_sno_list(v_i)
      returning rowid bulk collect into v_updated_rowid_list;
    for v_i in 1..v_updated_rowid_list.count loop
      dbms_output.put_line('updated rowid ' || v_updated_rowid_list(v_i));
    end loop;
end;
/
updated rowid AAApbXAALAAAHoUAAD
updated rowid AAApbXAALAAAHoUAAA
updated rowid AAApbXAALAAAHoUAAB
updated rowid AAApbXAALAAAHoUAAC
updated rowid AAApbXAALAAAHoUAAE

PL/SQL procedure successfully completed.

SQL>
SY,
Re: Find row count after update [message #686689 is a reply to message #686686] Mon, 21 November 2022 12:42 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Thanks SY. Both your solutions work!
Re: Find row count after update [message #686690 is a reply to message #686689] Tue, 22 November 2022 13:41 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Sy, If we need to update multiple columns, it doesnt work. Says it should be a subquery

declare
  type rowid_tbl_type is table of rowid;
  v_updated_rowid_list rowid_tbl_type;
begin
  update a
     set (a.sno, sno1) =
         (with driver as (select '100000227' new_sno, '000220842' sno
                            from dual
                          union all
                          select '002934741', '000250788'
                            from dual
                          union all
                          select '000020043', '000280330'
                            from dual
                          union all
                          select '000020130', '000280758'
                            from dual
                          union all
                          select '008087360', '000353719'
                            from dual)
           select d.new_sno, 99
             from driver d
            where d.sno = a.sno)
            where exists(with driver as (select '100000227' new_sno,
                                          '000220842' sno
                                     from dual
                                   union all
                                   select '002934741', '000250788'
                                     from dual
                                   union all
                                   select '000020043', '000280330'
                                     from dual
                                   union all
                                   select '000020130', '000280758'
                                     from dual
                                   union all
                                   select '008087360', '000353719'
                                     from dual)
              select null, null
                from driver d
               where d.sno = a.sno) returning rowid
                bulk collect
                into v_updated_rowid_list;


  for v_i in 1 .. v_updated_rowid_list.count loop
    dbms_output.put_line('updated rowid ' || v_updated_rowid_list(v_i));
  end loop;
end;
Re: Find row count after update [message #686691 is a reply to message #686690] Wed, 23 November 2022 08:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
This is a good one - sounds like a bug. It appears UPDATE SET tuple doesn't like CTE. Just move it into from caluse:

declare
  type rowid_tbl_type is table of rowid;
  v_updated_rowid_list rowid_tbl_type;
begin
  update a
     set (a.sno, sno1) =
         (
           select d.new_sno, 99
             from (select '100000227' new_sno, '000220842' sno
                            from dual
                          union all
                          select '002934741', '000250788'
                            from dual
                          union all
                          select '000020043', '000280330'
                            from dual
                          union all
                          select '000020130', '000280758'
                            from dual
                          union all
                          select '008087360', '000353719'
                            from dual) d
            where d.sno = a.sno)
            where exists(with driver as (select '100000227' new_sno,
                                          '000220842' sno
                                     from dual
                                   union all
                                   select '002934741', '000250788'
                                     from dual
                                   union all
                                   select '000020043', '000280330'
                                     from dual
                                   union all
                                   select '000020130', '000280758'
                                     from dual
                                   union all
                                   select '008087360', '000353719'
                                     from dual)
              select null, null
                from driver d
               where d.sno = a.sno) returning rowid
                bulk collect
                into v_updated_rowid_list;


  for v_i in 1 .. v_updated_rowid_list.count loop
    dbms_output.put_line('updated rowid ' || v_updated_rowid_list(v_i));
  end loop;
end;
/
updated rowid AAFvUmAAAAABNwrAAD
updated rowid AAFvUmAAAAABNwrAAA
updated rowid AAFvUmAAAAABNwrAAB
updated rowid AAFvUmAAAAABNwrAAC
updated rowid AAFvUmAAAAABNwrAAE

PL/SQL procedure successfully completed.

SQL>
SY.

[Updated on: Wed, 23 November 2022 08:39]

Report message to a moderator

Re: Find row count after update [message #686693 is a reply to message #686691] Wed, 23 November 2022 18:47 Go to previous message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Thanks! this works!
Previous Topic: unique timestamp
Next Topic: Function not working
Goto Forum:
  


Current Time: Fri Mar 29 07:20:32 CDT 2024