MYSQL clause not working: UPDATE WHERE MAX

Question

I have this piece of MySQL code that is driving me mad:

UPDATE Sells SET number = '$number' 
    WHERE id_student = '$id_student' AND id_book = '$id_book' 
    AND Hour = (SELECT MAX(Hour) FROM Sells WHERE id_student = '$id_student')

It doesn't blow up but it doesn't do what it is supposed to do either. If I take out the last part (the Hour clause) it works as it should, but when I add it as in the code above, it just does nothing (as if there where no Hours in the table, which is false). Hour type is INT.

Any ideas on how could I get the last clause working? Somehow it must be bad-written, but I can't see where.

See that: http://stackoverflow.com/questions/12242466/update-row-with-max-value-of-field — jmarceli, Jul 16 '13 at 12:31

score 0 · Answer 1 · answered Jul 16 '13 at 12:31

You need to include the filter on id_book in the subquery, as without it the MAX(Hour) returned by the subquery may be that of a record that has been filtered in the outer query:

UPDATE Sells
SET    number = '$number' 
WHERE  id_student = '$id_student'
   AND id_book = '$id_book' 
   AND Hour = (
         SELECT MAX(Hour)
         FROM   Sells
         WHERE  id_student = '$id_student'
            AND id_book = '$id_book'
       )

Note also that you appear to be quoting numeric literals as strings, which leads to unnecessary type conversion.

score 0 · Answer 2 · answered Jul 16 '13 at 12:33

0

It will only update if the record both matches the book id and the latest hour for that student. If one of those preconditions is false, no row will match.

You may need to add the book condition to the subselect as well.

answered Jul 16 '13 at 12:33

paxdiablo

854,327
234
1,573
1,953

With the complete php code (which I haven't included), this case is not possible. However, I found the solution here http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause Thanks! – nabla Jul 16 '13 at 12:55

MYSQL clause not working: UPDATE WHERE MAX

2 Answers2