CODEWITHSUNDEEP
mysqlsql
fozeh
fozeh

Reputation: 51

How to update all records in table with incremented values?

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?

Upvotes: 5

Views: 7888

Answers (4)

Pradeep Kumar
Pradeep Kumar

Reputation: 4141

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

Upvotes: 11

hugo
hugo

Reputation: 11

In MS SQL, create a temporary table with the function Row_Number() and then update your table joining with the temporary table

At the moment I am not sure if the syntax is totally right but I have decided to answer more because of the approach I have thought of

Example:

SELECT ROW_NUMBER() OVER(ORDER BY id asc) AS RowNr,
       id, name
INTO #tmp
FROM house

UPDATE  house
SET     h.position = t.RowNr - 1
FROM    house h
JOIN    tmp   t ON h.id = t.id

Upvotes: 1

Akshay
Akshay

Reputation: 3866

This would do

update house as hs 
inner join 
 (select id, (@row:=@row+1) as o from house h, (select @row:=0) as r) as hsjoin 
 on hsjoin.id = hs.id
set hs.order = hsjoin.o;

Basically use a variable and increment it, join the table and update the order

Upvotes: 1

Avi
Avi

Reputation: 1145

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;

enter image description here

Upvotes: 2

Related Questions