CODEWITHSUNDEEP
mysqlsql-update
Smile
Smile

Reputation: 2758

How to update order field's value sequentially for specified multiple id only?

I have following table in MySQL.

id | aname | orderNum | parentId
1  | A     | 2        | 1
2  | B     | 1        | 1
3  | C     | 4        | 2
4  | D     | 1        | 2
5  | E     | 3        | 2
6  | F     | 2        | 2
7  | G     | 3        | 1

I want to update orderNum field for following:

Set orderNum=1 when id=3 and parentId = 2    
Set orderNum=2 when id=7 and parentId = 2        
Set orderNum=3 when id=5 and parentId = 2        
Set orderNum=4 when id=6 and parentId = 2

In short I would like to set orderNum in increment order for ids (3,7,5,6). I will receive id in (3,7,5,6) order and then I want to update orderNum field in same sequence.

Can anybody help? How can I create an Update query for this?

I want to do this with MySQL.

Note: please see updated question

Upvotes: 1

Views: 135

Answers (2)

juergen d
juergen d

Reputation: 204766

Use FIELD() to get the index of the id in our sequence of ids

update your_table
set ordernum = field(id, 3,7,5,6) 
where id in (3,7,5,6)
and parentId = 2

Upvotes: 1

iamien
iamien

Reputation: 61

here is the sql query to do what you want.

Update TABLENAME set `orderNum`= `id`;

Upvotes: 1

Related Questions