Mysql DELETE proble

eelco

Hi,

I want to order a table in DESC order and then delete every row form row 5 to all the remaining rows.
So that the 4 highest values in the table are saved and the rest is deleted.

I tried to use the following syntax i found at http://www.mysql.com/doc/D/E/DELETE.html

DELETE field_name FROM table_name
WHERE 1>0
ORDER BY field_name DESC
LIMIT 5,1000000

But this way i get the following error:

1064: You have an error in your SQL syntax near 'WHERE 1>0
ORDER BY field_name DESC
LIMIT 5,1000000' at line 2

Can sombody help me with this?

techek

Have you tried leaving out the WHERE 1>0 and the field-name parts so that the query looks like this :

DELETE FROM <table> ORDER BY <attribute> DESC LIMIT 5,1000000

Note that this DELETES the rows - it doesn't delete the values in your field_name as your query could look like you'd try to do that.

Another approach could be to make a tombstone-attribute, set the tombstone-attributes for the rows that should be deleted and then delete all rows with the tombstones set. But then again that requires some programming too ...

PS : Write me if you need any help with this.

Anon

Hi,

Just a thought for you. If you are running your database with any form of transaction logging and you have a lot of rows, you\'ll be generating a large amount of log info.

To avoid that, a quicker way may be to do the next steps:

Populate a temporary table with the 4 rows that you want

http://www.mysql.com/doc/I/N/INSERT_SELECT.html

Basic syntax : insert into <temp table> select field_name FROM table_name
where <any where condition>
order by field_name DESC
limit 0,4

Truncate the original table. This is the quickest way to delete all the entries from any table:

http://www.mysql.com/doc/T/R/TRUNCATE.html

Move the rows from the temporary table.
Almost identical to 1, although you don\'t need any where order by or limit clauses.
Truncate the temporary table.

We do this to refresh some of the key tables we use. They\'re not huge (~30000 entries), but it runs v. fast.

Cheers

Justin

eelco

Techek,

Thanks for the help.
I tried the syntax and it didn't work i got almost the same error. I think mysql has a problem using ORDER BY and LIMIT in a DELETE query.

But i already thought of another way to solve the problem.

I used the following code:

$query="SELECT score, user_ID, mod_date FROM $dance_score_table ORDER BY score DESC, mod_date ASC;";
$result=mysql_query($query);
$last_score=mysql_result($result, $max_scores_limit-1, score);
$last_date=mysql_result($result, $max_scores_limit-1, mod_date);

		$query="DELETE FROM $dance_score_table 
				WHERE (score<$last_score)OR(score=$last_score AND mod_date>$last_date)";
		$result=mysql_query($query);
		if(!$result)error_message(sql_error());

There is some irrelevant code in this but i think you can see what i did here.

Thanks for the help anyway.

eelco

Rincewind,

Also thanks for the post.
I my case i wont be handling large data amounths. So the solution i found is good enough for me. But i realy learned some good tips from your post, so i appreciate the info verry mutch.

In my reply to Techek i desribed the way i did it.

If it appears to be a verry stupid way of handling the problem, please let me know.

Thanks for the help.

Eelco.

iifs044

Eelco,

If your table has a unique identifier column, you could use a nested query technique. For example:

DELETE FROM table_name
WHERE Id NOT IN (
SELECT Id
FROM table_name
ORDER BY field_name DESC
LIMIT 5
)

(I think mySQL supports nested queries, doesn't it?)

Anon

Mitch,

Unfortunately Mysql version 3.23 and 4.0 only support sub selects in "insert ..select" and "replace .. select".

Delete functionality has not yet been added (it's on the to-do list).

Info available at:

http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html

Cheers

Justin

eelco

thanks for the info.

Eelco