I have a simple table consisting of
id, channel_id, value and
timestamp. The combination of
channel_id,timestamp MUST be unique but the table has gotten corrupted. My ultimate goal is to add a unique index which fails due to the duplicates.
The duplicates are:
select channel_id,timestamp,count(*) cnt from data group by channel_id,timestamp having cnt>1
How can I delete the duplicates? I’m stuck since obviously I can’t get the row id from the duplicates sub select?
UPDATE I could probably do
delete d1.* from data d1 join data d2 on d1.channel_id=d2.channel_id and d1.timestamp=d2.timestamp where d1.id != d2.id;
but since the table has almost 1 billion records a cross-product does not seem like a good idea?