Get number of records deleted sql

Question

I want to get the row count of how many records have been deleted. The below query returns the number of records that will be affected, but

SELECT COUNT(*) FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 25 );

I need something like this, after the rows have been deleted, I can show the number of records that have been deleted on the front end.

SELECT COUNT(*) FROM suppliers(
DELETE from supplier(
    WHERE EXISTS
      ( SELECT customers.customer_name
        FROM customers
        WHERE customers.customer_id = suppliers.supplier_id
        AND customer_id > 25 ));

I don't want to run the query twice, one to get the number of records that will be deleted, if its greater than 0, then I run the second query to delete the records.

How are you invoking this SQL? This is important because most toolsets will return the number of affected records automatically when you run a DELETE command. For example, in Java's JDBC architecture, the `executeUpdate` command returns an integer representing the number deleted or updated (depending on the SQL). — schtever, Jul 20 '15 at 00:54
You should use the `RETURNING INTO` clause. The exact method depends on how you are making the call, but the documentation is here: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#LNPLS01354. — Gordon Linoff, Jul 20 '15 at 01:19
@schtever I'm using REST model services to invoke the query, all I get back is `json` and `jquery` `jsonp` to invoke. — user525146, Jul 20 '15 at 02:05

score 16 · Answer 1 · answered Jul 20 '15 at 01:33

You can use the RETURNING INTO clause to get all the rows being deleted -- and then count those.

An easier way for just the count is SQL%ROWCOUNT in PL/SQL code. Something like this:

BEGIN
    DELETE from supplier(
        WHERE EXISTS
          ( SELECT customers.customer_name
            FROM customers
            WHERE customers.customer_id = suppliers.supplier_id
            AND customer_id > 25 );
    dbms_output.put_line(SQL%ROWCOUNT);
END;

score -4 · Answer 2 · edited May 03 '23 at 20:15

-4

You can use

declare @countDelete int
set nocount on
delete tablename
where ..
select @@ROWCOUNT

edited May 03 '23 at 20:15

M--

25,431
8
61
93

answered Jul 20 '15 at 02:18

cutit

7
2

Get number of records deleted sql

2 Answers2