mysql delete from query using special id and date field

What i have is two columns specialid and date in tblSpecialTable, my table has duplicate specialID's, i want to delete from the tab开发者_运维技巧le where date column is the older date and where specialid's are duplicated.

See my example:

mysql> SELECT * FROM test;
+------+---------------------+
| id   | d                   |
+------+---------------------+
|    1 | 2011-06-29 10:48:41 | 
|    2 | 2011-06-29 10:48:44 | 
|    3 | 2011-06-29 10:48:46 | 
|    1 | 2011-06-29 10:48:52 | 
|    2 | 2011-06-29 10:48:53 | 
|    3 | 2011-06-29 10:48:55 | 
+------+---------------------+
mysql> DELETE t1 FROM test t1 INNER JOIN test t2 ON t1.id = t2.id AND t1.d < t2.d;
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+------+---------------------+
| id   | d                   |
+------+---------------------+
|    1 | 2011-06-29 10:48:52 | 
|    2 | 2011-06-29 10:48:53 | 
|    3 | 2011-06-29 10:48:55 | 
+------+---------------------+

See also http://dev.mysql.com/doc/refman/5.0/en/delete.html

You have to use a "double-barrelled" match on the combination of fields from another query.

DELETE FROM tblSpecialTable
WHERE CONCAT(specialid, date) IN (
    SELECT CONCAT(specialid, date)
    FROM (
        SELECT specialid, MAX(date) AS DATE, COUNT(*)
        FROM tblSpecialTable
        GROUP BY 1
        HAVING COUNT(*) > 1) x
    )

use a tmp table , set specialid column is unique. then use below sql: insert into tmp(specailid,date) values(select specialid,date from tplSpecialTable order by date desc)

DELETE FROM tblSpecialTable
  WHERE specialid NOT IN 
     (SELECT specialid FROM tblSpecialTable
                 GROUP BY specialid 
                 HAVING COUNT(table.date) > 1 
                 ORDER BY date 
                 LIMIT COUNT(table.date) - 1 )

This isn't a fancy single query, but it does the trick:

CREATE TABLE tmp as SELECT * FROM tblspecialtable ORDER BY date DESC;
DELETE FROM tblspecialtable WHERE 1;
INSERT INTO tblspecialtable SELECT * FROM tmp GROUP BY specialid;
DROP TABLE tmp;

The first line creates a temporary table where the values are ordered by date, most recent first. The second makes room in the original table for the fixed values. The third consolidates the values, and since the GROUP BY command goes from the top down, it takes the most recent first. The final line removes the temporary table. The end result is the original table containing unique values of specialid with only the most recent dates.

Also, if you are programatically accessing your mysql table, it would be best to check if an id exists first, and then use the update command to change the date, or else add a new row if there is no existing specialID. Also, you should consider making specialID UNIQUE if you don't want duplicates.