SQL problems

[Bouncinglemon]

this is driving me nuts, I hope someone can help!

//move all of the records analysed into the archive table.....
		$moverecords = "INSERT INTO gamestats_archive SELECT * FROM gamestats WHERE date='$selecteddate' ORDER BY entry_id ASC LIMIT $maxstats";
		$moveme = mysql_query($moverecords);
		print($moveme);
			
		//delete the old records in the game_stats table
		$delrec = "DELETE FROM gamestats WHERE date='$selecteddate' ORDER BY entry_id ASC LIMIT $maxstats";
		$delly = mysql_query($delrec);

Lets say there are 100 records on the same date in the gamestats I want to take 10 of those records and put them into the gamestats_archive and delete the records from teh game stats table.

The problem with the queries above is that the delete is not referencing the same records as the insert query, so the wrong records are deleted

does anyone know why? I going nuts

[asheep_uk]

I know it's not the answer, but if you left out all the limit and order stuff and just used the date - that would be 100% reliable.

[Bouncinglemon]

Im not sure it would as for a given date I might have 100 records and only want to move 10 of them

Thanks for your help

[Musicman]

Hi,

the ultimate solution: add an auto increment to the data
select id of some entries, then for all those id copy data to backup and delete them

Musicman

[Bouncinglemon]

Hi Musicman,

Thanks for your help, I'm not sure I understand what you mean could you explain it in a bit more detail?

Thanks

[Musicman]

Hi,

$q = mysql_query("select id from gamestats where ..... limit ....");
while($row = mysql_fetch_row($q))
{   $id = $row[0];
    mysql_query("insert into gamestats_archive select * from gamestats where id = $id");
    mysql_query("delete from gamestats where id = $id");
}

The "id" here must be a unique key - if you dont have one in your table, add an extra column

Musicman

[Bouncinglemon]

Hi Musicman,

Thanks for your help, that would solve the problem but would be fairly processor intensive given the volume of records I will be moving, sorry I didnt make it clear before but I will be moving upto 70,000-100,000 records with this query at one time. Surely there must be a way of reliably moving them all at once?

Thanks for your help

[Musicman]

Hi,

well I thought about ten or 100....

Here is just sql snippets that seem to work with mysql 4 but no older

select id from gamestats into temp table gamecopy where .... limit ....
select * into gamestats_archive from gamestats where id = gamecopy.id
delete from gamestats using gamestats, gamecopy where gamestats.id = gamecopy.id

Musicman
Musicman