Why does ExecuteNonQuery return 1 even if nothing was updated?

Question

I have a query like:

UPDATE messages SET Unread = 'N' WHERE id= '6'

To read affected rows, I use the value of ExecuteNonQuery(), but it always returns 1 even if nothing is changed. Saw the same problem here. Is this a bug or is this behavior normal?

Answer 1

For ExecuteNonQuery to return 1 there must be a record WHERE id = '6'. Now, if you only want to update the row if the value is different then change the query:

UPDATE messages SET Unread = 'N' WHERE id = '6' AND Unread <> 'N'

If you were to run that query and the value of Unread was already 'N' then it would return 0 rows.