Count deleted records after a DELETE

user_2594961 0
Viewed 6244

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.

sql-delete sqlite
5 Answers

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 :)

user_11915590

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;
    }
user_126715770
Related