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.
You can do a
JOIN
with the same table and perform theUPDATE
likeEDIT:
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