Pacific-Design.com

    
Home Index

1. SQL Language

2. 08 DELETE Duplicates

SQL Language / 08 DELETE Duplicates /

MySQL - Delete Duplicate Records

-- MySQL Delete Duplicate Records
ALTER IGNORE TABLE customer
ADD UNIQUE INDEX idx_name (id, company);


MySQL - Delete Duplicate Records and Keep Latest

-- MySQL delete duplicate records but keep latest
DELETE test FROM test a
INNER JOIN (
    SELECT max(date_time) AS last_date_time, email
    FROM test
    GROUP BY email
    HAVING count(*) > 1
) b ON b.email = a.email

WHERE a.date_time < b.last_date_time;


DELETE FROM test
WHERE date_time NOT IN 
(
    SELECT max(date_time)
    FROM test
    GROUP BY email
)


MSSQL - Delete Duplicate Records

-- Delete Duplicate Records in MSSQL
WITH Duplicate AS
(
  SELECT id, ts, price,
    ROW_NUMBER() OVER(PARTITION BY id, ts ORDER BY (SELECT 0)) AS rn
  FROM stockData
)
DELETE FROM Duplicate WHERE rn > 1


Delete Invalid Domains

DELETE FROM database.table WHERE NOT (domain LIKE '%.__' OR  domain LIKE '%.___');
DELETE FROM database.table WHERE domain LIKE '%\'%';
DELETE FROM database.table WHERE domain REGEXP "\\\\";
DELETE FROM database.table WHERE domain like '%--%';
DELETE FROM database.table WHERE domain like "%\n%";
DELETE FROM database.table WHERE domain like '%+%';