0

I try to increment a field in mysql but it seems that it is not really working for some reasons ...

That is the query I run in mysql query:

UPDATE profil_perso
SET profile_views = profile_views +1
WHERE id = 30537

In the status bar it says : 1 row affected by the last command. No resultset returned. But it didn't seemed to work. At first I thougth that it was simply because there were no rows at all. So then I ran this query:

SELECT *
FROM profil_perso
WHERE id = 30537

And found 1 row and the profile_views field is still at 0...

Any ideas of why this would be that way ?

[edit] profile_views = 1 (set manually) at this moment and it still doesn't work.

Improve this question
2
  • you make select in the same transaction? Commented Apr 8, 2009 at 16:47
  • So what was the case? ;) Commented Apr 8, 2009 at 16:49

4 Answers 4

Reset to default
3

Didn't you forget to commit a transaction when working with an InnoDB table?

UPDATE:

Since it's a MyISAM, I'll ask yet a couple of stupid questions:

  1. Are you absolutely sure you're issuing UPDATE and SELECT against one database?

    I once had a large farm of databases with identical schemata, and this used to be a problem when someone deleted something from the wrong database.

    • Aren't you using some kind of client caching on your client? What client are you using?

And try to REPAIR your table, this also may be the issue.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Man ... how foul I am ... it's working totally ok now. I was selecting from the _dev db instead of the _production db. Lol! It's weird, the _production db was in bold both times. Totally working now.
2

Just to verify - is profile_views zero, or null? If you add 1 to null, you still get null, so that could be your problem.

ETA:

So it isn't null. Next question would be, do you have autocommit on or, if not, did you issue a "commit"? Could be that it was updated, but your update never committed.

Improve this answer

2 Comments

It is at zero. I thought at first that it would be this and set it to 1. Still remains at 1 after the update.
The table is MyISAM so it's not supposed to affect it, isn't it ?
1

If it's null, won't increment.

UPDATE profil_perso
SET profile_views = IF(profile_views,profile_views+1,1)
WHERE id = 30537
Improve this answer

Comments

0

Add some parens and a coalesce to see if that fixes it:

UPDATE profil_perso
SET profile_views = ( COALESCE(profile_views,0) + 1 )
WHERE id = 30537
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.