MySQL glitch - updates do not update

[mstembri]

UPDATE SET foo = 'hello' WHERE yaz = 'world'

does not return an error, but the old data remains.

This started happening after my hosting company changed co-hosts. They don't have an answer and certain crucial features of my site are not working on account of this problem.

Are others experiencing a similar problem?

[freddydoesphp]

maybe its just a case of going too fast you forgot to put a table name between UPDATE and SET

[mstembri]

I don't think so. At least I've been using that syntax for my update queries for 4 months without a problem. (I'll give your syntax a try)

edited -- oh, i did forget to add the table name in the example above. the update queries in my scripts do have the table specified... UPDATE tablename SET foo = 'hello' WHERE yaz = 'world' -- edited


What is strange is that I can get some update queries to work. For instance if I update a record WHERE [info: where the column was created before my host changed co-hosts move /info], it works.

I'm baffled. I pulled down the entire database and tested on my linux box at work -- the udpate statements work fine (with the syntax above).
<Edited by mstembri on 01-04-2001 at 05:36 PM>

[Ed Sullivan]

As freddy said, you missed the table part:

UPDATE table SET field='val' WHERE field='val';

http://www.mysql.com/documentation/m...section=UPDATE

[freddydoesphp]

How can you run a table update command without specifying the tablename

[mstembri]

Ed, check the edit to my last post (altered while you made your post)...


Ok, I performed the following queries on the identical table - one on georgiaoffroad.com, the other on my work's intranet.

georgiaoffroad: MySQL version 3:23:10, PHP version 4.0.3pl1
local intranet : MySQL version 3:22:22, PHP version 3.0.15

(Both serve pages from linux boxes running apache servers.)

UPDATE review SET power = 'fooyazfoo' WHERE reviewid = 234;


* Column power is BLOB. -- this was added when the table was created 4 months ago.

* Column reviewid is INT (5) -- this was added after my site was transferred from one server to another.

On the georgiaoffroad site, there were no errors, but again the data remained as though the update was not successful.

On the local intranet, there were no errors, and the data was successfully updated.


MORE>>>>

When I performed this query:

UPDATE review SET power = 'fooyazfoo' WHERE model='93patriot406' AND username = 'testuser';

* Column model is VARCHAR(10). -- this was added when the table was created 4 months ago.

* Column username is VARCHAR (8) -- this was added when the table was created 4 months ago.

The georgiaoffroad site DID update the power column with the new information.

The local intranet also updated the information.


Hopefully this better explains the problem.

- Mike

[freddydoesphp]

I know this may seem like bunk info but I have had this happen to me where on one machine my query works fine but on another I needed single quotes around my where clauses if there are more than one.
UPDATE review SET power = 'fooyazfoo' WHERE ((model='93patriot406') AND (username = 'testuser'));

[mstembri]

As I mentioned in my last post, the WHERE clause that you provided in your example (above - with model and username) works fine on both versions of mysql.

The problem is using the reviewid column in a clause (the column that was added on the new server). I'm trying your example as I type...

Nope. Doesn't work.

Hmph...

[mstembri]

One more bit of information.

DELETE FROM review WHERE id = '234';

-- no errors, but the row is not deleted.