How to increment a field in mysql
Published on September 26th, 2007 by DannyT. Filed under MySQLNothing much to see here, just something I’m always forgetting the syntax for as I am often switching between SQL Server, MySQL and Oracle.
I needed to implement an incrementing numeric field for a “reportOrder” field, couldn’t rely on the primary key because I will later be adding the facility to re-order the table based on this field.
SET @pos=0;
INSERT INTO myNewTable(someField, reportOrder)
SELECT fieldName, @pos:=@pos+1
FROM myOldTable
ORDER BY reportOrder;
All i’m doing is assigning the incremented variable value and at the same time reading it in the query. Like I said, nothing major just something I seem to need regularly and couldn’t find a rownum (oracle) equivilent for MySql
Roger Maynard
January 23rd, 2008 at 5:29 pm
Very nice little bit of code…. thanks.
Having come from Foxpro background, functions like RECNO() are missed.
Until now that is!
Brillliant!
Roger
Ben Baker
June 9th, 2009 at 3:17 pm
Hey Danny great example.
I had an issue running a local server (MAMP), where the combined SQL:
SET @pos=0;
INSERT INTO myNewTable(someField, reportOrder)
SELECT fieldName, @pos:=@pos+1
FROM myOldTable
ORDER BY reportOrder;
Did not work in PHP, although it did work in phpMyAdmin…
Turns out that my PHP could not group queries I think. So I had to do this:
// set var in sql
$query = “SET @pos=0″;
$result = mysql_query($query);
mysql_free_result($result);
// now query will run
$query = “INSERT INTO myNewTable(someField, reportOrder)
SELECT fieldName, @pos:=@pos+1
FROM myOldTable
ORDER BY reportOrder;”
Thanks again,
B