开发者

SQL UPDATE statement - returns success but 0 rows affected

开发者 https://www.devze.com 2023-02-02 03:22 出处:网络
I am sending an SQL UPDATE query, and the mySQL database is behaving strangely. It returns a successful response, but 0 rows are affected. The code is:

I am sending an SQL UPDATE query, and the mySQL database is behaving strangely. It returns a successful response, but 0 rows are affected. The code is:

UPDATE place 
SET city='Qatar' AND country='Qatar'
WHERE placeid='25'

Response: Successful 0 row(s) affected. ( Query took 0.0003 sec )

There is definitely a record with placeid='25' in the database. If I do a SELECT * FROM place WHERE placeid='25' I get the record returned.

Any ideas? thanks!

EDIT:

Table structure:

SQL query: describe place; 
Rows: 9

Field         Type     Null   Key    Default  Extra
title         text     开发者_JS百科NO            NULL     
description   text     NO            NULL     
latitude      float    NO            NULL     
longitude     float    NO            NULL     
image         text     NO            NULL     
placeid       int(11)  NO     PRI    NULL     
country       text     YES           NULL     
city          text     YES           NULL     
address       text     YES           NULL     


Try with this :

UPDATE place 
SET city='Qatar', country='Qatar'
WHERE placeid=25

Because your AND sounds strange in your query.


I noticed that MySQL is 'smart' and does not update a record to a value that is already in database. When I run an update statement from Workbench I got the first time the response (updating from 'a' to 'b'):

update t.t set s='b' where k > 0|1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0

The second time I get the response (updating from 'b' to 'b'):

update t.t set s='b' where k > 0|0 row(s) affected Rows matched: 1 Changed: 0 Warnings: 0

Fortunate the row is never the less locked in the transaction.


Are you sure placeId is varchar but not numeric? have you tried without '' ?

UPDATE place  SET city='Qatar' AND country='Qatar' WHERE placeid=25
0

精彩评论

暂无评论...
验证码 换一张
取 消