CODEWITHSUNDEEP
mysqlsql
stack
stack

Reputation: 10228

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?

Upvotes: 0

Views: 53

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 2

Killan
Killan

Reputation: 341

Maybe something like

How do I UPDATE from a SELECT in SQL Server?

and use the id+1 for table2.

Upvotes: 0

Related Questions