MySQL Affected Rows after INSERT, UPDATE and DELETE

After an INSERT, UPDATE or DELETE, MySQL can report how many rows were affected by the statement.

At SQL level, run ROW_COUNT() immediately after the statement:

UPDATE student
SET mark = mark + 5
WHERE class = 'Four';

SELECT ROW_COUNT() AS affected_rows;

In modern PHP, use PDO rowCount() or MySQLi affected_rows. The old PHP mysql_affected_rows() function shown in earlier versions of this page belonged to the removed mysql_*() extension and should not be used in current PHP.

MySQL ROW_COUNT() Top ↑

ROW_COUNT() returns the number of rows affected by the statement executed immediately before it on the same MySQL connection.

DELETE FROM student
WHERE id > 30;

SELECT ROW_COUNT() AS deleted_rows;

If five rows were deleted, the second statement returns 5.

Affected Rows after UPDATE Top ↑

Suppose one student already has a mark of 80:

UPDATE student
SET mark = 80
WHERE id = 7;

There are two different questions:

• Did the WHERE clause match a row?
• Did the stored value actually change?

With normal MySQL client behavior, an UPDATE affected-row count commonly reflects rows whose values changed. If student 7 already had mark 80, the affected-row count can be 0 even though the WHERE condition matched the row.

If you need to distinguish "no such row" from "row already had this value", check the business condition separately rather than treating affected rows as the only proof that a record exists.

Changed rows vs matched rows Top ↑

Some MySQL client settings, including the CLIENT_FOUND_ROWS behavior, can make UPDATE reporting use matched rows instead of changed rows. For application logic, know how your client library is configured rather than assuming every connection reports UPDATE counts identically.

Affected Rows after DELETE Top ↑

For DELETE, the affected-row count tells you how many rows were actually removed:

DELETE FROM student
WHERE class = 'Three';

SELECT ROW_COUNT() AS deleted_rows;

If no rows satisfy the WHERE condition, the count is normally 0.

Affected Rows after INSERT Top ↑

A normal single-row INSERT affects one row:

INSERT INTO student
    (name, class, mark, gender)
VALUES
    ('Ravi', 'Four', 72, 'male');

SELECT ROW_COUNT() AS inserted_rows;

A successful multi-row INSERT can report more than one affected row:

INSERT INTO student
    (name, class, mark, gender)
VALUES
    ('Ravi', 'Four', 72, 'male'),
    ('Anita', 'Five', 81, 'female'),
    ('Aman', 'Six', 67, 'male');

This statement normally affects three inserted rows.

If you need the generated AUTO_INCREMENT identifier rather than the number of rows inserted, use LAST_INSERT_ID() / the application's insert-ID API.

Affected Rows with PHP PDO rowCount() Top ↑

PDOStatement rowCount() is the modern Plus2net PHP route for checking affected rows after INSERT, UPDATE or DELETE.

<?php
require 'config.php';

$id=7;
$mark=80;

$stmt=$dbo->prepare(
    "UPDATE student
     SET mark=:mark
     WHERE id=:id"
);

$stmt->bindValue(
    ':mark',
    $mark,
    PDO::PARAM_INT
);

$stmt->bindValue(
    ':id',
    $id,
    PDO::PARAM_INT
);

$stmt->execute();

$affected=$stmt->rowCount();

echo 'Rows affected: '.$affected;

The dedicated PDO rowCount() tutorial covers INSERT, UPDATE, DELETE and transaction examples in more detail.

Do not treat UPDATE rowCount() == 0 as an automatic failure Top ↑

if($stmt->rowCount() === 0){
    // Could mean no matching row OR no actual value change.
}

This is a major correction to the old password-update example that assumed "affected rows is not 1" automatically meant the update failed.

Affected Rows with MySQLi Top ↑

MySQLi exposes the affected-row count through the database connection:

<?php
$stmt=$connection->prepare(
    "DELETE FROM student WHERE id=?"
);

$stmt->bind_param(
    'i',
    $id
);

$stmt->execute();

$affected=(int)$connection->affected_rows;

echo 'Rows deleted: '.$affected;

Use the same active connection that executed the statement.

Do Not Use Affected Rows to Count SELECT Results Top ↑

Affected-row functions are mainly for data-changing statements. If your goal is to count records matching a SELECT condition, ask SQL to calculate the count:

SELECT COUNT(*) AS total_students
FROM student
WHERE class = 'Four';

With PDO, retrieve that aggregate using fetchColumn(). Do not rely on PDO rowCount() for portable SELECT-result counting.

TRUNCATE and DDL Statements Top ↑

Do not use affected-row reporting as a count of how many records a TRUNCATE TABLE removed:

TRUNCATE TABLE student;

TRUNCATE is a table-level operation with different semantics from DELETE. If the application must know how many rows existed before truncation, count them before the operation.

The same general caution applies to DDL statements such as CREATE, ALTER and DROP: affected-row APIs are not a substitute for understanding the statement's own result and metadata.

Affected Rows inside a Transaction Top ↑

Affected-row information can help application logic inside a transaction, but the count itself does not commit the change.

<?php
$dbo->beginTransaction();

try{
    $stmt=$dbo->prepare(
        "DELETE FROM student
         WHERE id=:id"
    );

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->execute();

    $affected=$stmt->rowCount();

    if($affected === 1){
        $dbo->commit();
    } else {
        $dbo->rollBack();
    }
}
catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
    echo 'Database operation failed.';
}

This works naturally for a DELETE-by-unique-ID workflow because either one row is deleted or no row is deleted. UPDATE needs the changed-vs-matched-row caution explained earlier.

Python Example Using the Same Affected-row Concept Top ↑

Plus2net also uses the affected-row concept in Python. In the Tkinter/MySQL Treeview delete example, SQLAlchemy checks result.rowcount after the DELETE and removes the Treeview row only after the database reports that one row was deleted.

This is a good example of the same SQL result being consumed by a different application language: MySQL performs the DELETE, while Python checks the affected-row result before updating the interface.

Legacy mysql_affected_rows() Top ↑

Earlier versions of this Plus2net page used code like this:

<?php
// Historical example only. The mysql_* extension was removed from PHP.

$query=mysql_query(
    "UPDATE table_name
     SET password='...'
     WHERE userid='...'"
);

$affected=mysql_affected_rows();

Do not use this API in new or maintained PHP code. Use PDO rowCount() or MySQLi affected_rows.

Common Affected-row Mistakes Top ↑

Using mysql_affected_rows() in modern PHP Top ↑

The old mysql_*() extension was removed. Use PDO or MySQLi.

Assuming UPDATE rowCount() == 0 always means failure Top ↑

Zero can mean no matching row or that a matching row already contained the requested value.

Using affected rows to count SELECT records Top ↑

Use COUNT(*) when the application needs the number of records matching a SELECT query.

Using MAX(id) as confirmation that your INSERT succeeded Top ↑

Use the generated insert-ID API for the same connection. MAX(id) can belong to another session.

Expecting TRUNCATE to report deleted rows like DELETE Top ↑

TRUNCATE has different semantics. Do not use affected-row APIs as its deleted-record count.

Checking affected rows after another statement has already executed Top ↑

Read the affected-row result immediately after the statement you are evaluating.

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: