Rupert Stokoe <[EMAIL PROTECTED]> wrote:
>
> I'm having problems trying to construct a delete query
> from a table. Below is a scaled down version of the
> table
>
> host user date id
> -----------------------------
> mars john 27-6 1
> pluto dave 27-6 2
> venus jane 27-6 3
> mars john 28-6 4
> saturn tom 28-6 5
> pluto dave 29-6 6
>
>
> What I want to do is purge old records for each host
> from the table. If I use the query:
>
> SELECT max(date), host FROM table GROUP BY host;
>
> Then I can get the latest record produced for each
> host. There are never two records for a host produced
> on the same date. So basically I want to remove all
> records *other than* the latest records. If MySQL
> supported sub queries then this would be easy:
>
> DELETE from table where id not in (SELECT max(date)
> FROM table GROUP BY host)
>
> Is this something I'm just going to have to do client
> side? I figured I could run the query above to get all
> id's of records to keep and then form a long string of
> them i.e.
>
> keepString = "(1, 4, 7, 9, 12, 15, 17 etc.)"
>
> and then run ("DELETE FROM table WHERE date NOT IN" +
> keepString) .
>
> This seems a bit messy tho, and keepstring would
> contain over 100 id's if used.
If you use 4.0.x you can retrieve max date into temporary table and then use DELETE
with LEFT JOIN to remove records.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
