CODEWITHSUNDEEP
sqlitesql-delete
yaylitzis
yaylitzis

Reputation: 5534

Count deleted records after a DELETE

I try to count how many records will be deleted after a DELETE command:

SELECT COUNT(*) FROM BOXES
WHERE EXISTS  ( 
    DELETE FROM BOXES WHERE product='25043620' AND Order='0846'
)

I get an syntax error near delete, but I can't figure out which is it.

Upvotes: 4

Views: 8305

Answers (5)

MelS
MelS

Reputation: 119

I also had this question, but am using the C library. There's a function that gets that for you: https://www.sqlite.org/c3ref/changes.html.

I did this...

    // using String = std::string;
    // execute: runs the query
    uint32_t Db::executeWithCount(const String& query) {
        uint32_t count = 0;
        if(execute(query, detail::empty_callback, nullptr)) {
            // number of rows affected by most recent INSERT, UPDATE or DELETE
            count = sqlite3_changes(_db);
        }

        return count;
    }

Upvotes: 0

dalcam
dalcam

Reputation: 1077

For those who came here for SQLServer - you would use:

SET NOCOUNT OFF
DELETE FROM BOXES WHERE product='25043620' AND Order='0846'
SELECT @@ROWCOUNT

Im not sure if this works in SqlLite however, would be cool if someone could confirm :)

Upvotes: 0

yaylitzis
yaylitzis

Reputation: 5534

After some digging, I figured out... after

DELETE FROM BOXES WHERE product='25043620' AND Order='0846'

I ask database for changes with:

SELECT changes()

and I get how many rows was deleted.

Upvotes: 15

Dave Warren
Dave Warren

Reputation: 1

You could try putting the statement into a loop with the argument as the loops condition and then just let the loop count for you as it increments.

Upvotes: 0

MikeT
MikeT

Reputation: 56953

I don't think that you can combine the count and the delete. Do them individually.

Here's and example :-

Example - Using SQL EXISTS Clause

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the SQL FROM clause when you are performing a delete, you can use the SQL EXISTS clause.

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customers.customer_name = 'IBM' );

This SQL DELETE example would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.

If you wish to determine the number of rows that will be deleted, you can run the following SQL SELECT statement before performing the delete.

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

This from SQL: DELETE Statement

Upvotes: 1

Related Questions