(INSERT) SET column = MAX(column) + 1

[xtopolis]

Hi,

I wanted to know if I can update a column value to equal the max of the column + 1 (like auto increment, but not the same)

 

INSERT INTO table SET pos = MAX(pos) + 1  // is what I would logically want to do, but it gives me invalid use of group function

 

Do I need to do a "SELECT MAX(pos) as curpos FROM table";  And then use php to ++1 on it, then do ($curpos++);

"INSERT INTO table SET pos = '$curpos'"

 

or can I do it as a one liner, per say.

 

 

simple structure, example data:

[id][pos][name]      id is auto_inc, pos[int,unique], name[varchar255]

 

[3][1][File 3]

[1][2][file 1]

[5][3][banana]

[9][4][data]

[4][5][file]

 

[fenway]

You'd need an UPDATE statement, but yes, you can use the SET... the way you described above.