mySQL UPDATE query returns "0 rows affected"

Question

I have this query:

UPDATE phonecalls 
   SET Called = "Yes" 
 WHERE PhoneNumber = "999 29-4655"

My table is phonecalls, I have a column named PhoneNumber. All I want to update is a column named Called to "yes".

Any idea what I am doing wrong? when I return my query it says 0 rows affected.

Answer 1

If the such value already exists, mysql won't change it and will therefore return "0 rows affected". So be sure to also check the current value of called

Answer 2

Another reason for 0 affected rows that I have observed: wrong data type. If the column you want to update is an integer or boolean, and you set it to a string, it won't be updated - but you will also get no error.

To sum up the other strategies/ideas from this post:

1. Check with a SELECT statement, whether your WHERE works and returns results.
2. Check whether your columns do already have the value you want to set.
3. Check if your desired value suits the data type of the column.

Answer 3

If the values are the same, MySQL will not update the row (without triggering any warning or error), so the affected row count will be 0.

Answer 4

The problem might be that there are no records with PhoneNumber == "999 29-4655".

Try this query:

SELECT * FROM phonecalls where PhoneNumber = '999 29-4655'

If it doesn't return anything, then there are no rows that match.

Answer 5

For the benefit of anyone here from Google, this problem was caused by me because I was trying to append to an empty field using CONCAT().

UPDATE example SET data=CONCAT(data, 'more');

If data is NULL, then CONCAT() returns NULL (ignoring the second parameter), so the value does not change (updating a NULL value to be a NULL value), hence the 0 rows updated.

In this case changing to the CONCAT_WS() function instead fixed the problem.

Answer 6

Try select count(*) from phonecalls where PhoneNumber = "999 29-4655"; That will give you the number of matching rows. If the result is 0, then there isn't a row in the database that matches.-

Answer 7

Check to make sure this returns some result.

SELECT * FROM phonecalls WHERE PhoneNumber = '999 29-4655'

If it doesn't return any result than the filter WHERE PhoneNumber = '999 29-4655' is not correct.

Answer 8

1. Does it say Rows matched: 1 Changed: 0 Warnings: 0? Then maybe it's already set to that value.
2. Did you try single quotes vs. double quotes?
3. "999 29-4655" is the space a space or a tab and is it consistent in your query and the database?

Answer 9

That's my sugestion:

UPDATE `phonecalls` SET `Called` = 'yeah!' WHERE `PhoneNumber` = '999 29-4655' AND `Called` != 'yeah!'

And make sure with the case-sensitive name of table and field`s.

Answer 10

Just ran into an obscure case of this. Our code reads a list of records from the database, changes a column, and writes them back one by one. The UPDATE's WHERE clause contains only two conditions: WHERE key=? AND last_update_dt=?. (The timestamp check is for optimistic locking: if the record is changed by another process before we write ours, 0 rows are updated and we throw an error.)

But for one particular row the UPDATE was failing- zero rows effected.

After much hair-pulling I noticed that the timestamp for the row was 2019-03-10 02:59. In much of the U.S. that timestamp wouldn't exist- Daylight Savings Time causes the time to skip directly from 2:00 to 3:00. So I guessed that during the round trip from MySQL to Java back to MySQL, some part of the code was interpreting that timestamp differently from the rest, making the timestamps in the WHERE clause not match.

Changing the row's timestamp by one hour avoided the problem.

(Of course, the correct fix is to abolish Daylight Savings Time. I created a Jira but the U.S. Government has not responded to it yet.)

Answer 11

In my case, I was trying to update a column of text to correct a truncation problem with it. Trying to update to the correct text was yielding 0 rows updated because the text in the row wasn't changing.

Once I extended the column in the table structure to accommodate for the correct number of characters, I was able to see the desired results.