CODEWITHSUNDEEP
phpmysqlsql
user305015
user305015

Reputation: 103

Mysql Insert Order Number By VARCHAR Field

I have a question about inserting row order number by spesific order type.

Products table has OrderNumber field. I want to programaticly add new line to appropriate OrderNumber by its name. If the reference column would be integer it has to be easy like that

update products set OrderNumber=OrderNumber+1 where Price>555

Is there similar way for varchar field like

update products set OrderNumber=OrderNumber+1 where Name>'bla%'

Thank you

Upvotes: 3

Views: 859

Answers (4)

Florin Ghita
Florin Ghita

Reputation: 17643

You can use STRCMP('text', 'text2')

update products 
set OrderNumber=OrderNumber+1 
where STRCMP(Name, 'bla') = 1;

I missunderstood your point. Can you try something like this?

SET @rownum:=0;
update 
set OrderNumber=@rownum:=@rownum + 1
from products
order by Name;

Upvotes: 2

simonecampora
simonecampora

Reputation: 397

You can simply run an update query with a sequential number like in here

Upvotes: 2

themhz
themhz

Reputation: 8424

you mean something like update products set OrderNumber=OrderNumber+1 where Name like 'bla%'

Upvotes: 1

Marc B
Marc B

Reputation: 360742

Sounds like a bad design. Why not simply have a plain-jane auto_increment field and order by that? Every new record would by defnition have a higher ID than any of its predecessors.

Upvotes: 1

Related Questions