update with max value of the column is not working in 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.

Answer 2

You have a typo in the statement, you used UPADTE instead of UPDATE.

One problem is, don't quote the subquery. You have used single quotes, which means the expression select max(order_id)+1... was interpreted as a text literal (a varchar). But you clearly don't want that (I guess order_id is a number). What you want instead is to evaluate the subquery. However, if you try:

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

then MySQL doesn't allow it (I didn't know about that). Other databases such as PostgreSQL allow it. So you might need two statements:

select @id = coalesce(max(order_id), 0) + 1 FROM upload_video;
UPDATE `upload_video` SET order_id = @id WHERE `video_id` = 22;

Please note this works in MySQL but not in other databases.