CODEWITHSUNDEEP
mysqlsqlsql-update
Timespace
Timespace

Reputation: 5671

Any way to distinguish the cases of 0 affected row of MYSQL update?

Suppose I have a table named 't'

---------------
| key | value |
---------------
| 1   | abc   |
| 2   | def   |
---------------

Consider two MYSQL queries

  1. UPDATE t SET value='abc' WHERE key=1
  2. UPDATE t SET value='abc' WHERE key=3

Executing both queries also give the 'affected rows' is 0 (That is, do NOT update any row) because first query is an non-updating update and second is an non-matching update.

Is there any way to distinguish these two cases?

Upvotes: 3

Views: 1183

Answers (1)

mnagel
mnagel

Reputation: 6854

if you only want the number of 'matched' rows (and no longer the number of 'changed' rows), you can set CLIENT_FOUND_ROWS as described here:

http://dev.mysql.com/doc/refman/5.5/en/mysql-affected-rows.html

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

Upvotes: 1

Related Questions