CODEWITHSUNDEEP
mysql
francoisrv
francoisrv

Reputation: 2617

mysql: increment maximum field value on insert

I have a table with a field called 'sort' which contains sorting number. When I add a new row, I would like the sort field to be filled with the maximum existing value + 1. I tried this:

insert into highlights set sort=max(sort)+1

but I get a 1111 error "Invalid use of group function"

If I try with a subquery,

insert into highlights set sort=(select max(sort) from highlights)+1

I get a 1093 error since apparently I cannot subquery the same table I am inserting into.

Any ideas? Thanks!

Upvotes: 1

Views: 2151

Answers (1)

D.G
D.G

Reputation: 85

Try with this

 insert into highlights set sort=(select max(h1.sort)+1 from highlights h1);

Upvotes: 2

Related Questions