3

In my activity_logs, it contains columns: material_name, user_id, mod_result (this marks a test if Pass/Fail), cert_links. Somehow the users generate twice the entry of the material_name with the cert_links column left blank.

I can list duplicates to all user_id with:

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 the duplicate entries that has mod_result = 'Fail' and cert_links = ''

Unihedron
  • 10,902
  • 13
  • 62
  • 72
user3349519
  • 31
  • 5

2 Answers2

0

you can use:

Delete from activity_logs WHERE mod_result = 'Fail' AND cert_links = '' or ''NULL'
Mani
  • 888
  • 6
  • 19
0

3 steps:

Step 1: Move the non duplicates (unique tuples) into a 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;

Step 3: rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;

Arun
  • 87
  • 13
  • Thanks but I don't wish to delete the duplicate records of the entire table; what I want is to remove duplicate titles (material_name) with reference to its user_id. So if John Doe has 2 ABC Materials recorded I want to delete the rest and leave only one entry. – user3349519 Mar 04 '14 at 05:08
  • How can I group them per user_id and list down all material_names then keep one record/delete duplicates for every material? – user3349519 Mar 04 '14 at 05:12