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  |
 |
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  |
 |
Littlefoot
Messages: 21823
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>
|
|
|
|
| Re: Find row count after update [message #686680 is a reply to message #686677] |
Sat, 19 November 2022 06:54 |
Solomon Yakobson
Messages: 3305
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>
|
|
|
|
|
|
|
|
| Re: Find row count after update [message #686684 is a reply to message #686682] |
Sat, 19 November 2022 20:07 |
Solomon Yakobson
Messages: 3305
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>
[Updated on: Sat, 19 November 2022 20:08] Report message to a moderator |
|
|
|
|
|
| Re: Find row count after update [message #686686 is a reply to message #686685] |
Sun, 20 November 2022 11:05 |
Solomon Yakobson
Messages: 3305
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>
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>
|
|
|
|
|
|
| Re: Find row count after update [message #686690 is a reply to message #686689] |
Tue, 22 November 2022 13:41 |
|
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 |
Solomon Yakobson
Messages: 3305
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>
[Updated on: Wed, 23 November 2022 08:39] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 24 23:31:03 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
