can UPDATE clause in MYSQL using PHP update multiple records in one query?

Question

well i have this messages table with sample values like these:

msg_id  recipient_id   read   locked   new
  0         1            N       Y      Y
  2         1            Y       N      N

ok, so lets just say this is a messaging table, and i want to reset all messages addressed to recipient with id=1

i was wondering why

UPDATE `messages` SET `new`='Y',`read`='N',`locked`='N' where `recipient_id`=1;

doesn't work, MYSQL always returns 0 affected rows... can anyone help me?

to robert gamble: yes, im sure the values were changed, since my purpose for this update query is to reset the data i was using for the testing phases :D

Answer 1

MySQL will return 0 affected rows if no changes were made to any records, i.e. the records matching the WHERE clause already had the values specified in the SET clause, are you sure they didn't?

Answer 2

You have some floating single-quotes in there. You may be assigning one string to another or something.

It's ok to just say

UPDATE messages
SET new = 'y', read = 'N', locked = 'N'
WHERE recipient_id = 1

Answer 3

great, thanks doofledorfer it was just some typo error when i posted the questions but my qoutes where in their right places, although it seems that when i removed the qoutes --> `
the query worked perfectly