Rimozione record duplicati in una tabella MySQL

← Torna a MySQL

-- ============================================================
-- CONFIGURAZIONE
-- ============================================================
SET @tabella = 'dhb_esxibackupvms';
SET @campi   = '`date`, `hour`, `company`, `server`, `vm`, `type`, `element`, `message_id`';

-- ============================================================
-- ESECUZIONE
-- ============================================================

-- Step 1: Crea tabella temporanea con gli ID da tenere
SET @sql_crea = CONCAT(
    'CREATE TEMPORARY TABLE IF NOT EXISTS _ids_da_tenere AS ',
    'SELECT MIN(id) AS id FROM `', @tabella, '` ',
    'GROUP BY ', @campi
);
PREPARE stmt FROM @sql_crea; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Step 2: Elimina i duplicati
SET @sql_delete = CONCAT(
    'DELETE FROM `', @tabella, '` ',
    'WHERE id NOT IN (SELECT id FROM _ids_da_tenere)'
);
PREPARE stmt FROM @sql_delete; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Step 3: Pulizia
DROP TEMPORARY TABLE IF EXISTS _ids_da_tenere;