Hi all, I am new to SQL and am using a simple data base in MySQL to store scores from a simple flash game. the database has one table and is laid out as follows.+----+---------+-------------+---------+| id | name | time_string | time_ms |+----+---------+-------------+---------+| 17 | testing | 000000 | 54 || 2 | dan2 | 00:00:00:45 | 1547 || 3 | dan3 | 00:00:00:45 | 3564 || 4 | dan4 | 00:00:00:45 | 483 || 5 | dan5 | 00:00:00:45 | 4564 || 6 | dan6 | 00:00:00:45 | 1210 || 7 | dan7 | 00:00:00:45 | 1135 || 8 | dan8 | 00:00:00:45 | 625 || 9 | dan9 | 00:00:00:45 | 7845 || 10 | dan10 | 00:00:00:45 | 6512 || 11 | dan11 | 00:00:00:45 | 3214 || 12 | dan12 | 00:00:00:45 | 7213 || 13 | dan13 | 00:00:00:45 | 9842 || 14 | dan14 | 00:00:00:45 | 8472 || 15 | dan15 | 00:00:00:45 | 3210 || 16 | dan16 | 00:00:00:45 | 7213 |+----+---------+-------------+---------+16 rows in set (0.02 sec)
Now I want to do something that I thought would be simple but now I think I may be missing something about how SQL works. I want to sort the list by time_ms shortest time at the top then delete every row with a time_ms greater than the 10th entry.So I try the following code delete from xmasgame where time_ms > (select time_ms from xmasgame order by time_ms limit 9,1);
And get the following error.ERROR 1093 (HY000): You can't specify target table 'xmasgame' for update in FROM clause
Now I get that SQL cannot do this as it cannot reference a table being updated in the where statement. But what I can't figure out is a way around this?Any help would be much appreciated. 
Delete all rows after the 10th row.
Posted - 2009-11-20 : 20:54:05
