stack stack - 5 months ago 15
SQL Question

How to set the value of a row into previous row?

I have a table like this:

// numbers
+----+--------+
| id | numb |
+----+--------+
| 1 | zero |
| 2 | one |
| 3 | two |
| 4 | three |
| 5 | four |
| 6 | five |
| 7 | six |
| 8 | seven |
| 9 | eight |
| 0 | nine |
+----+--------+


Now I'm trying to copy/paste the value of each row (just
numb
column) to the upper column. So this is expected result:

+----+--------+
| id | numb |
+----+--------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 6 | six |
| 7 | seven |
| 8 | eight |
| 9 | nine |
| 0 | zero |
+----+--------+


Actually I can do that by PHP. I mean I can fetch all rows and shift one itam and then update them. But I want to know can I do that by pure mysql?

vkp vkp
Answer

All the rows except the max of id will get updated. The max id will still have the same numb. (in this case 9,'eight')

update tablename t1
JOIN tablename t2 on t1.id = t2.id-1
set t1.numb = t2.numb;

Sample Fiddle