Forum Stats

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

Discussions

Categories

count the row before a delete

francy77
francy77 Member Posts: 260 Bronze Badge
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook