MySQL: Update user record with value from next user record

Question

I have a table which looks like this:

ID    | uid   | vcode   | vcode_next
1       1       abc
2       2       def
3       2       ghi
4       1       jkl
5       1       mno

and I need to update vcode_next with the next vcode for a given user ID. So the result should be:

ID    | uid   | vcode   | vcode_next
1       1       abc       jkl
2       2       def       ghi
3       2       ghi       
4       1       jkl       mno
5       1       mno

I've seen some similar questions which have different demands selecting the update value, but I can't quite figure out how to implement this for the next value for a given user-id.

Any hints would be much appreciated.

Answer 1

You can do a JOIN with the same table and perform the UPDATE like

UPDATE table1 a 
    JOIN table1 b ON a.uid = b.uid AND a.ID < b.ID
SET a.vcode_next = b.vcode;

EDIT:

The posted query works fine and gives exact rsult per your post. See this demo fiddle http://sqlfiddle.com/#!9/4f020/1 for a proof. It results in below after UPDATE