How to update all records in table with incremented values?

Question

I have a table "House":

id | name | order
1  | riw  | 0
2  | hnm  | 0
4  | vse  | 0
5  | tes  | 0

And I would like to simply receive:

id | name | order
1  | riw  | 0
2  | hnm  | 1
4  | vse  | 2
5  | tes  | 3

So I tried:

UPDATE house SET position = position + 1

but how can I increment this values?

Answer 1

Use id column to update:

UPDATE house SET order = id - 1

If you feel id can start from n value other than 1, try:

SET @position:=0;
update house
set order=@position:=@position+1

Answer 2

In Oracle you can use ROWNUM

In My SQL : Rank can be used. Also You can create temp table with identity column which is auto-incremented. Then insert into temp table from your main table. And finally truncate your main table and insert back from temp table.

Also you can look at below query which you can modify for your purpose.

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees
LIMIT 5;