更新列的最大值在mysql中不起作用

Question

I have tried to set the max value for the particular column but that is not working for me. I do not know where i'm going wrong.

UPDATE `upload_video` 
             SET order_id ='select max(order_id)+1 
             FROM upload_video' 
             WHERE `video_id` = 22

This is my query i run the select max(order_id)+1 from upload_video query separately which is giving the result. But if i use this query in update query, the query is executing without error. But the order_id is not updating properly. please help me

Answer 1

Your query is almost correct in standard SQL, you only need to use brackets () instead of apostrophe ':

SET order_id = (SELECT MAX(...) ...)

but MySQL doesn't allow you to update a table while selecting from the same table, a workaround is to use a subquery that calculates the value that you need, and to join your subquery with the table you need to update:

UPDATE
  upload_video JOIN (SELECT COALESCE(MAX(order_id),0)+1 max_id
                     FROM upload_video) s
SET
  upload_video.order_id=s.max_id
WHERE
  video_id=22

Please see fiddle here.