Forum Moderators: open
1) If there is a DB error, display an error message.
2) If there are no affected rows because the update is the same as the current field, display 'no change'
3) If there is no update because no matching record is found, display 'no record found'
4) If update is successful, display 'success'
The problem is distinguishing between cases 2 & 3. I tried using affected_rows, but it is 0 in both cases. I also tried num_rows, but it is 1 in both cases.
Is there a simple way to distinguish between these, w/o having to first check whether the record exists, then updating it?
By that I mean the DBMS doesn't inspect the values already in the DB for that row and compare them to the new values you're including in the UPDATE to determine if it needs to do the update. It always attempts to perform the update. If there is a row (or rows) that matches the WHERE clause on your update, it sets the row(s) to the new value(s) even if the row already contained the exact same values you are trying to SET.
For example, if I have a table tMyTable with columns MyKey, Col1, Col2, Col3:
if tMyTable has a row in it where
MyKey = 1
Col1 = 2
Col2 = 3
Col3 = 4
If I issue the following statement
UPDATE tMyTable
SET Col1 = 2, Col2 = 3, Col3 = 4
WHERE MyKey = 1
then the DBMS is still going update the row with the same values that were already there and the rows affected will = 1.
It does not compare the new values you are SETting to those already in the DB and come to the conclusion that it doesn't need to update it because they already have the same values.
[edited by: ZydoSEO at 6:12 pm (utc) on Jan. 16, 2008]
It shows 1 affected row if there is a changed value, and 0 affected rows if there is either no changed value or no matching record found.
[dev.mysql.com...]
