1

I'm just stuck with a simple mysql query, I want to update the row having max Id of a table, and I was trying something like but it doesn't work

UPDATE inbox i
INNER JOIN messages m ON i.message_id = m.id 
SET i.read = 0
WHERE m.conversation_id = 10
AND i.user_id = 1
ORDER BY i.id DESC
LIMIT 1

I've also tried sub query but it doesn't work either

Need some help on it.

Thanks

Brad Larson
  • 170,088
  • 45
  • 397
  • 571
MZH
  • 1,414
  • 4
  • 29
  • 57
  • Sorry my mistake, there is a join in my update query so I didn't put the right query before – MZH Nov 10 '12 at 11:09

2 Answers2

2

In MySql you can't update a table if you have a subquery that references the same table, but you could sostitute the subquery with JOINS. I would do this, it's a trick but it works:

UPDATE
  inbox inner join (select max(id) as maxid from inbox) mx on inbox.id = mx.maxid
SET inbox.`read` = 0

EDIT: I see you edited your question, so i have to edit my answer:

UPDATE
  inbox
  INNER JOIN (select max(inbox.id) as maxid
              from
                inbox inner join messages
                on inbox.message_id = messages.id
              where
                messages.conversation_id=10
                and inbox.user_id=1) mx
  on inbox.id = mx.maxid
SET inbox.`read` = 0

Your subquery returns the maximum id based on the conversation_id and the user_id you want, then you join inbox whith the maximum id to select just the row you want, and you can then update just that row.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • i also found this solution, with a different approach: http://stackoverflow.com/questions/1513206/update-multiple-rows-using-limit-in-mysql but i like my solution more :) – fthiella Nov 10 '12 at 11:22
  • Thanks for replying, I've tried it UPDATE inbox i INNER JOIN (SELECT MAX(inbox.id) AS maxid FROM inbox INNER JOIN messages ON inbox.message_id = messages.id WHERE messages.conversation_id=10 AND inbox.user_id=1) mx ON inbox.id = mx.maxid SET i.read = 0 but I'm getting Unknown column 'inbox.id' in 'on clause' – MZH Nov 10 '12 at 11:32
  • If I put it just max(id) instead of max(inbox.id) it give Column 'id' in field list is ambiguous – MZH Nov 10 '12 at 11:33
  • i fixed max(inbox.id) and i had an alias on set, fixed that also. now my code should be ok... i see you are using an alias for inbox, try to use this ON clause: `on i.id = mx.maxid` – fthiella Nov 10 '12 at 11:36
1

You need to escape reserved words in MySQL like read with backticks. You can also use limit to update just the greatest record.

UPDATE inbox
SET `READ` = 0
order by id desc
limit 1

SQLFiddle example

juergen d
  • 201,996
  • 37
  • 293
  • 362