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

Question

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?

@lolka_bolka What? Where `id = 9` the value should be `nine`. — stack, Jun 17 '16 at 13:56
indead, the first value is a question, put at the last or use negative id, and so, need a create and not an update. — Killan, Jun 17 '16 at 13:57

score 2 · Accepted Answer · answered Jun 17 '16 at 13:58

2

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

answered Jun 17 '16 at 13:58

Vamsi Prabhala

48,685
4
36
58

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

Maybe something like

How do I UPDATE from a SELECT in SQL Server?

and use the id+1 for table2.

edited May 23 '17 at 11:52

Community

1
1

answered Jun 17 '16 at 13:56

Killan

321
1
6
14

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

2 Answers2