Geek asks and answers

Delete duplicate tables

In my activity_logsit contains columns: material_name, user_id, mod_result(this means checking if the Pass / Fail), cert_links. One way or another, users generate a double entry material_namewith a column cert_linksleft blank.

I can list duplicates for everyone user_idwith:

SELECT user_id, material_name, mod_score, cert_links, start_time 
FROM activity_logs 
WHERE mod_result = 'Pass' AND cert_links = ''

I want to delete duplicate entries with mod_result = 'Fail'andcert_links = ''

+3
source share
2 answers

you can use:

Delete from activity_logs WHERE mod_result = 'Fail' AND cert_links = '' or ''NULL'
0
source

3 steps:

Step 1: Move non-duplicates (unique tuples) to the temporary table

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN To remove duplicates BY];

Step 2: delete the old table

DROP TABLE old_table;

3: new_table old_table

RENAME TABLE new_table TO old_table;

0

More articles: