a 'safe' way to move data?
I'm trying to move data between 2 tables. INSERT INTO new_table SELECT * FROM old_table LIMIT 5; DELETE FROM old_table LIMIT 5; This is the only process that deletes data from old_table, can I be *sure* that the limit in these 2 queries will address the same data set? (if I don't limit to small numbers in the LIMIT, I/O gets too high, so I'm moving data slowly in batches) Thanks, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
Hi Christian, Before delete teh data from old_table, just have a backup. Create table new_table_bck select * from old_table limit 5; But i feel, instead of using limit, try to get data based on some date or other condition, so that you are sure that same data gets insert and also deleted from old table regards anandkl On 3/30/07, Ian P. Christian [EMAIL PROTECTED] wrote: I'm trying to move data between 2 tables. INSERT INTO new_table SELECT * FROM old_table LIMIT 5; DELETE FROM old_table LIMIT 5; This is the only process that deletes data from old_table, can I be *sure* that the limit in these 2 queries will address the same data set? (if I don't limit to small numbers in the LIMIT, I/O gets too high, so I'm moving data slowly in batches) Thanks, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
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. - Original Message - From: Ian P. Christian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, March 30, 2007 8:18 AM Subject: a 'safe' way to move data? I'm trying to move data between 2 tables. INSERT INTO new_table SELECT * FROM old_table LIMIT 5; DELETE FROM old_table LIMIT 5; This is the only process that deletes data from old_table, can I be *sure* that the limit in these 2 queries will address the same data set? (if I don't limit to small numbers in the LIMIT, I/O gets too high, so I'm moving data slowly in batches) Thanks, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
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? -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
Dan Nelson wrote: 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. Ah of course - a far better idea. Thanks :) -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
