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.

Answer 1

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;