Forum Stats

  • 3,814,867 Users
  • 2,258,922 Discussions
  • 7,892,882 Comments

Discussions

count the row before a delete

francy77
francy77 Member Posts: 260 Bronze Badge

Hi all,

In a store procedure, I need to delete some row, and I want to know how many rows I'm going to delete before to really delete them,


for example I can write

select count(k_id) into count from tab1 where id_s=12;

and after

delete from tab1 where id_s=12;


But I'm asking if there is a different way to accomplish this.

thanks

Tagged:

Best Answer

  • Alex Nuijten
    Alex Nuijten Member Posts: 237 Silver Badge
    edited Feb 2, 2021 3:52PM Answer ✓

    Why not do it the other way around?

    delete from tab1 where id_s = 12;

    and using sql%rowcount immediately after the DELETE, you know how many were removed.

    BEGIN
       delete from tab1 where id_s = 12;
       dbms_output.put_line (to_char (sql%rowcount)||' removed');
    end;
    
    francy77

Answers

  • Alex Nuijten
    Alex Nuijten Member Posts: 237 Silver Badge
    edited Feb 2, 2021 3:52PM Answer ✓

    Why not do it the other way around?

    delete from tab1 where id_s = 12;

    and using sql%rowcount immediately after the DELETE, you know how many were removed.

    BEGIN
       delete from tab1 where id_s = 12;
       dbms_output.put_line (to_char (sql%rowcount)||' removed');
    end;
    
    francy77
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,892 Red Diamond
    edited Feb 2, 2021 3:41PM

    Hi,

    Why do you want to count the rows?

    Are you going to do something special before deleting them, or maybe not delete them at all, depending on how many rows are involved? If so, what you posted is the best way.

    Do you just need to know how many rows were deleted? In PL/SQL, you can do that after the DELETE, like this:

    DELETE ...;
    rows_deleted := SQL%ROWCOUNT;
    

    Make sure you save the value immediately after the DELETE. Other statements may automatically change SQL%ROWCOUNT (just like DELETE did).

    francy77