Nothing 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.
INSERT INTO myNewTable(someField, reportOrder)
SELECT fieldName, @pos:=@pos+1
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