List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 30 2007 2:44pm
Subject:Re: a 'safe' way to move data?
View as plain text  
In the last episode (Mar 30), Ian P. Christian said:
> Brent Baisley wrote:
> >No, you can't assure the same data will be addressed without at
> >least including an order by. Even then you would need to make sure
> >that the first X records in the order would not change. For
> >instance, if you order by entered_date DESC, then the data set would
> >change because any new records would get included in the LIMIT.
> 
> Will it not always use the natural order of the table in
> selects/deletes, and therefore return results in the order in which
> they were inserted?

If you know that no-one else has inserted or deleted records between
your two commands, the commands will return the same records.  

To be completely safe, you would want to use innodb tables, then select
only the primary key of the 50,000 records you're interested in, using
the "FOR UPDATE" keyword (to keep others from modifying those records
while you're doing the move).  Then "INSERT INTO newtable SELECT * FROM
oldtable WHERE primarykey in ( your 50,000 keys )", then "DELETE FROM
oldtable WHERE primarykey in ( your 50,000 keys )", then COMMIT, which
will cause your insertions and deletions to be truly atomic.

-- 
	Dan Nelson
	dnelson@stripped
Thread
a 'safe' way to move data?Ian P. Christian30 Mar
  • Re: a 'safe' way to move data?Ananda Kumar30 Mar
  • Re: a 'safe' way to move data?Brent Baisley30 Mar
    • Re: a 'safe' way to move data?Ian P. Christian30 Mar
      • Re: a 'safe' way to move data?Dan Nelson30 Mar
        • Re: a 'safe' way to move data?Ian P. Christian30 Mar