How to determine if MySQL UPDATE actually matched any rows with WHERE clause?

Question

I want to run an update like such in PHP

// pseudocode
UPDATE invoice SET due_date=? WHERE invoice_id=? AND creater_id=?;
IF AFFECTED_ROWS == 1 THEN
UPDATE invoice_item SET price=? WHERE invoice_id=?

For added security, I appended creater_id to ensure that the code only updates if the logged in user is the invoice creator, otherwise, the system will not update.

I originally intended to check this condition using AFFECTED_ROWS. But eventually after much frustration, I realise AFFECTED_ROWS return 0 if all the new values are the same as the old values. This means that even if I have different values for the invoice_item, they will not be updated.

Other than doing a SELECT before the UPDATE, is there SQL query or PHP functions that will tell me if the UPDATE matched any row, so that I can proceeed to UPDATE invoice_item accordingly?

"I realise AFFECTED_ROWS return 0 if all the new values are the same as the old values" - So, you still want to count the numbers of rows affected along with the previous records with the same invoice_item? — Logan Wayne, Mar 21 '14 at 05:45
@LoganWayne I updated my post. I *originally* wanted to use AFFECTED_ROWS, but realised this is not going to help. — Jake, Mar 21 '14 at 05:49

score 1 · Accepted Answer · answered Mar 21 '14 at 05:47

You can use ROW_COUNT() and if you read that it explains that when connecting to the DB you can specify the CLIENT_FOUND_ROWS flag which will give the number of rows found for the update, regardless of if they have the same value of what you're updating with.

Hope this helps.

mseifert · Answer 2 · 2014-03-21T05:59:58.750

I've taken this from my code so things like $link need to be in place- but it shows what you are interested in

function update() {
    $q = "UPDATE table SET field1=? WHERE field2 = $value"; 
    /* create a prepared statement */
    $stmt = mysqli_stmt_init($link);
    if (mysqli_stmt_prepare($stmt, $q)) {
        mysqli_stmt_bind_param($stmt, "s",  $field1);
        mysqli_stmt_execute($stmt);

        if(mysqli_stmt_errno($stmt)){
            echo("Sql Error: ".$q. ' Sql error #: '.mysqli_stmt_errno($stmt). ' - ' . mysqli_stmt_error($stmt);
            return false;
        }
        else{
            $numrows =  mysqli_stmt_affected_rows($stmt);
            if (mysqli_stmt_errno($stmt) == 0 || mysqli_stmt_errno($stmt) ==''){
                // numrows = -1 is flag no error and no rows affected
                $numrows = ($numrows ==0?-1:$numrows);
            }
            else{
                echo("Sql Error: ".$q. ' Sql error #: '.mysqli_stmt_errno($stmt). ' - ' . mysqli_stmt_error($stmt);
                return false;
            }
             /* close statement */
            mysqli_stmt_close($stmt);
            return $numrows;
        }
    }
}

This looks like it will still return 0 if new and old values are identical, because you are only checking for `mysqli_stmt_errorno()`? — Jake, Mar 21 '14 at 05:53
My understanding is that if there are no errors and I know the record exists, then the old and new values are identical. I return false if there was an error, # of affected rows if > 0 and -1 if no changes made to the record. You could return 0 and use === to check for the difference between false and 0. — mseifert, Mar 21 '14 at 05:57

score 0 · Answer 3 · answered Mar 21 '14 at 06:12

As per documentation on ROW_COUNT():

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful.

Your query:

Other than doing a SELECT before the UPDATE, is there SQL query or PHP functions that will tell me if the UPDATE matched any row

You can also use ROW_COUNT() within an UPDATE or any other DDL or DML statement.

Example: Using your pseudocode:

// pseudocode
UPDATE invoice SET due_date=? WHERE invoice_id=? AND creater_id=?;
IF ( ROW_COUNT() >= 1 ) THEN
   UPDATE invoice_item SET price=? WHERE invoice_id=?
END IF;

Or else, you can try like:

UPDATE invoice SET due_date=? WHERE invoice_id=? AND creater_id=?;
UPDATE invoice_item SET price=
       (case when ( row_count() >= 1 ) then ? else price end)
WHERE invoice_id=?;

Before setting the parameter value check again for the row_count() value to decide whether to set values for 1 or more parameters.

nl-x · Answer 4 · 2014-03-21T07:04:30.603

0

You can take this back to 1 query and not worry about affected rows:

UPDATE
  invoice
  left join invoice_item on invoice_item.invoice_id = invoice.invoice_id
SET
  invoice.due_date = ?, -- the WHERE will only let this happen if it will be changed
  invoice_item.price = ? -- the WHERE will only let this happen if it will be changed
WHERE
  invoice.invoice_id = ?
  and invoice.creater_id = ?
  and invoice.due_date != ? -- here compare the new due_date to the one already in the db

edited Mar 21 '14 at 07:04

answered Mar 21 '14 at 06:14

nl-x

11,762
7
33
61

"In the OP you state you only want to do it, if there is only one" -- actually I only update if there is only 1 invoice (not invoice_item). – Jake Mar 21 '14 at 06:32

How to determine if MySQL UPDATE actually matched any rows with WHERE clause?

4 Answers4