I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id.
Normally I would use the following statement to remove duplicates:
DELETE FROM emails WHERE email_id NOT IN ( SELECT MAX(email_id) FROM emails GROUP BY record_id, from_add, to_add, subject, body)
However, body is datatype
text, so it cannot be compared/grouped. It gives the following error message on the select query:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
What is the best way to identify duplicates with the
text datatype? Do I have to convert the column to