Why is query sometimes not executing

Hi guys

I have this query

$update = "
            UPDATE Users t1 SET
                NewMail =
                    CASE
                        WHEN (NewMail=0 OR NewMail IS NULL) THEN 1
                        ELSE NewMail + 1
                    END
            WHERE
                UserId = $rUserId
            LIMIT 1
        ";
        mysql_query($update);

it is executed with other queries..

IT DOES NOT FAIL.

But sometimes it does not update and the NewMail keeps beeing 0. why is that?
LVL 1
tim_carterAsked:
Who is Participating?
 
tim_carterConnect With a Mentor Author Commented:
I found out that sometimes my value was less than zero.
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

probably becuse you use the LIMIT 1
this means update the last value

change the value to a larger number or remove.
0
 
tim_carterAuthor Commented:
It is only 1 user that it should update at this point? why would i run through every record?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Aaron ShiloChief Database ArchitectCommented:
just to be clear you need to update only one record at a time ?
0
 
tim_carterAuthor Commented:
YES. it is only one user..

like UserId=2


0
 
Aaron ShiloChief Database ArchitectCommented:
that isent what i asked.

when you use Userid= 2 will you allways retrive only one row from the table?
0
 
tim_carterAuthor Commented:
and that is what i need. but sometimes it does not update the NewMail value
0
 
Aaron ShiloChief Database ArchitectCommented:
hi
when you run this query

select * from Users
                    where NewMail=0 OR NewMail IS NULL
                     and UserId = $rUserId

notice i removed the LIMIT 1

how meny rows come bac ?
0
 
tim_carterAuthor Commented:
what has this query to do with what i want?

1st of the query you have done here will return almost anything because you have not encapsulated (newmail)

MY STATEMENT should update 1 user. and if the user has the value 0 or NULL in newmail it should set the value to 1, else it should add to the current value,.
0
 
Muhammad WasifCommented:
>>But sometimes it does not update and the NewMail keeps beeing 0. why is that?
Are you sure that $rUserId is not empty during this time? May be query is failing due to some eorr or $rUserId has incorrect value. Use mysql_error() to detec the error and print the query to debug the issue in this way.

mysql_query($update) or die("Error in Query: $update - ". mysql_error());

Open in new window

0
 
tim_carterAuthor Commented:
wasifg

I am already doing that in the script..

It does not fail. And it is randomly it happens.
0
 
tim_carterAuthor Commented:
Can it have something to do with CASE?
0
 
tim_carterAuthor Commented:
less than zero. My own error
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.