Search code examples
mysqlselectsql-updateinnodb

MySQL update returns 0 rows affected while select returns results

select * from t_circle
where status = 0 and author_phone = 13511111111
and id in (
1,
2,
3
)
;

returns 3 rows with status of 0.

But the following update query with same conditions returns 0 rows affected:

udpate t_circle set status = 2
where status = 0 and author_phone = 13511111111
and id in (
1,
2,
3
);

Is there a reason for this? I have tried start transaction and commit, but still 0 rows affected. Other questions' answers suggest to run select first and make sure the rows are changed since if new value == old value, it is considered affected. I have excluded these 2 possibilities.

Note: this issue is found in our production server(Yeah I know I shouldn't but I have to) with InnoDB engine. I could modify the contents in GUI client like DBeaver and click save and the changes take effect, but not with sql statements. I wonder if it has anything to do with my account authorization?

Resolved! It is because I misspelled UPDATE. When I use mysql> in command line, update with 'udpate' just gives Query OK, 0 rows affected. My mysql's version is 5.7

Solution

  • By comparing GUI generated sql queries when I change something in GUI and my own queries. I found out the reason is I misspelled update to udpate. Now I begin to wonder how could mysql not complain about syntax error and just returns with 0 rows affected?

    Well, at least now I know what to do when something odd happens.