First off, let us prepare a sample schema for the `fla_urls` table before manipulation. It could be as follows:
create table fla_urls (
id INT AUTO_INCREMENT PRIMARY KEY,
loc VARCHAR(255) NOT NULL,
-- Add other columns as needed
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Use the SQL query to identify duplicate occurrences of values in the loc column of the `fla_urls` table.
select loc, count(*) as occurrences from fla_urls group by loc having count(*) > 1;
The next, create a temporary table to store the minimum id for each duplicate loc as below:
create temporary table temp_min_ids as
select MIN(id) as min_id
from fla_urls
group by loc
having COUNT(*) > 1;
Then, conduct to delete duplicate records from the original table.
delete from fla_urls
where id not in (select min_id from temp_min_ids);
Finally, Drop the temporary table.
drop temporary table if exists temp_min_ids;
Beside, you can use nested subquery in DELETE statement and SELECT statement.
delete from fla_urls
where loc in (
SELECT loc
FROM (
SELECT loc
FROM fla_urls
GROUP BY loc
HAVING COUNT(*) > 1
) AS duplicate_locs
);