On 6/28/05, C. Ed Felt <[EMAIL PROTECTED]> wrote:
> So, in short, (since a paragraph will get confusing):
>
> 1. SELECT all records in the CDR table after a requested date (usually
> 24 hours).
> 2. Store these records in a huge array.
> 3. Find all repeats on the 'sessid' field and store this in an array.
> 4. Delete all repeats (save one copy of each repeat CDR).
>
> Is there a MySQL query, (version 3), to select all rows that have one or
> more matching rows on a specific field ('sessid')? This would
> essentially combine steps 1, 2 and 3 in one MySQL query.
DISCLAIMER: I haven't been able to test the following, as I don't have
mysql installed on this box, and don't want to go through the trouble.
However, I remember doing similar things, and according to my memory,
this might work...
SELECT unique_id, sessid, COUNT(*) AS n
FROM table WITH date_column > ?
GROUP BY sessid HAVING n > 1
and then looping over the results:
DELETE FROM table
WHERE sessid=?
AND NOT unique_id = ?
This solution assumes the existence of a unique ID field so that you
can distinguish records. Otherwise there's no way (in the query) to
exclude on of the duplicate records from the DELETE.
Jacob Fugal
.===================================.
| This has been a P.L.U.G. mailing. |
| Don't Fear the Penguin. |
| IRC: #utah at irc.freenode.net |
`==================================='
