List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 19 2010 1:35am
Subject:numbering the result set rows for insertion into another table
View as plain text  
I have the following pseudo code running on mysql 4.x:

set @cnt:=0;
insert ignore into dest_table
      select t1.field1,  t1.field2,  t1.field3,  t2.field1,
t1.field3, t2.ts, @cnt:=@cnt+1
      from table1 as t1 left join table2 as t2 using (field1, field2)
      order by t2.ts;

This works perfectly to sequentially number the result set rows
inserted into dest_table in order of t2.ts (a timestamp field).

In my upgrade to mysql 5.1.14-community, the numbers returned by @cnt
are not in order... they trend upward from 0 to the number of records
inserted, but they're far from "in order"... so somehow mysql is
inserting the rows in some strange order.

How can I fix this so it works in both mysql 4.x and 5.x?

Many thanks.

-Hank
Thread
numbering the result set rows for insertion into another tableHank19 Sep
  • Re: numbering the result set rows for insertion into another tableMySQL)20 Sep
    • Re: numbering the result set rows for insertion into another tableHank20 Sep