MySQL auto-id function provides the ability to automatically create a new id for each new record on insert.  This works great for unique ids, but sometimes more logic is required.

For a recent assignment we needed to populate a table that reflects seating in a stadium.  The seating was typical of most venues consisting of Aisles and Seats.  For example, A-1, A-2, A-3, B-1, B-2, B-3, and so on. When the table was initially populated, the seat_number field was all set at it’s default value of ‘1′.  So we needed sequential numbers for the seats, but the count had to start over with each new Aisle.  This could have been done easily in a scripting language, but what fun is that!  We wanted to accomplish the numbering in one regular SQL statement.

This problem would be easily solved using Oracle’s rownum functionwhere we can get a sequential id for each row within a resultset.  But, alas, MySQL doesn’t have a rownum function.  What we did find, however, is a wonderful hack on Mark Malakonov’s blog that duplicates this functionality for MySQL.

The final query, which only updates Aisle ‘A’ is here:

update venue_seat set seat_number =
(select @rownum:=@rownum+1 rownum FROM (SELECT @rownum:=0) r)
where aisle = 'A'


These fields obviously relate to a specific schema, however the @rownum trick can be used to generate sequential numbers for your tables as well. If you need to update or populate a table with sequential numbers but need some additional logic, this is a handy trick to have in your toolbox.

Share This