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.
Thank you!!
All the examples I found until yours were for SELECT only, I really needed one for UPDATE.
Perfect!
Great job, you make me save time with this sentence.
Solution for a slightly different problem on the same lines though :
Ex. : If you have to generate auto IDs but from 1 for every occurrence :
Ex. :
Col1 Col2
A 1
A 2
A 3
B 1
B 2
B 3
All you have to do is :
create table test(col1 int not null, col2 int auto_increment not null,primary key(a,b));
insert into test values(‘A’);
insert into test values(‘A’);
insert into test values(‘A’);
insert into test values(‘B’);
insert into test values(‘B’);
insert into test values(‘B’);
Simple ain’t it?!
Thanks Kiran – that’s interesting, i didn’t know composite keys with an auto-id would work like that. I think this @rownum code is still pretty useful for various things but I’ll definitely have to remember your trick as well.
this works great! just what I was looking for! Only problem is that i’m using it to assign ordered numbers to rows based on their time value (I am ordering based on the time value). it works the first time but if a record changes to a different time value it’s not re-assigning the ordered number values based on the new order of the query results. Any ideas?
$sql = “UPDATE Logs SET Place = (SELECT @rownum:=@rownum+1 rownum FROM (SELECT @rownum:=0) r) WHERE ElapsedTime !=’00:00:00′ and CheckpointID = ‘$CheckPointID’ Order by ElapsedTime”;
Never mind. It works great when I run it in MySQL. I’ve been looking for a simple solution for weeks. Thanks! Can’t get it to work yet in my php script though so I’m working on it..
@jessica
Hi Jessica,
At least this isn’t *years* after your post
I had the same problem after reading this post via google so I thought I would post my solution
The cause of your problems is that the ORDER BY part of your clause is applied after the set of rows is generated. So, first MySQL is going to fetch all the rows that match your query and then apply the sort.
in the context of @rownum, MySQL will only generate x rows, i.e. WHERE… @rownum < count. The ORDER BY is applied after the result set is limited.
To get around this, put your whole query inside a sub-select without the @rownum. Apply the ORDER BY on the inner query. Then, on the outer query, apply the @rownum business. i.e.
select
a.a, a.b, a.c, …
@rownum := @rownum + 1
from ( … your original query with ORDER BY …) a, (select @rownum := 0) r
where @rownum < ( … row count to limit …)
For future reference