mySQL UPDATE query returns "0 rows affected"

Admin 1
Viewed 75857

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.

sql-update mysql
11 Answers

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

Cassy_252530

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.

user_939790

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.-

user_2379550

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.

Yada_450660
  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?
user_2637090

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.

user_2597640

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.)

user_24368780

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.

user_9499110
Related