Students records in MySQL are entered again in more than one table after entering the email wrong. H-CodePudding

There are three tables - student, exams, sports.

STUDENT : sid(primary, auto increment), email, fname, lname, address, standard
EXAMS : eid(primary, auto increment), sid(foreign) ename, date, result
SPORTS : spid(primary, auto increment), sid(foreign) spname, date, score

Data was added into the DB, but after sometime a guy in my project realized that he entered some emails wrong. Then, instead of editing the emails, he tried to add new entries of some of those students but not all data (he missed adding entries to exams/sports table for some students) . He did that for random students.

I tried this query to get a clear understanding

SELECT a.sid, a.fname,
CASE WHEN EXISTS (SELECT * FROM EXAMS e WHERE a.sid = e.sid) THEN 'YES' ELSE 'NO' END,
CASE WHEN EXISTS (SELECT * FROM SPORTS s WHERE a.sid = s.sid) THEN 'YES' ELSE 'NO' END
FROM STUDENT a;

How do I find which records I need to delete and which I need to update.

CodePudding user response：

As existing copy of data has been inserted, instead of updating the emails, you can check using the other attributes in STUDENT table for more than one copies of data. Assuming he has entered data for other attribtes correctly, you can check using this:

SELECT fname, lname, address, standard, MAX(sid) 
FROM STUDENT c GROUP BY fname, lname, address, standard HAVING count(*) > 1;

As sid is AUTO_INCREMENT, then for the same student, the later inserted IDs are duplicate. The above query finds those, use that to delete those IDs from your STUDENT table. Subsequently delete all those IDs from the rest tables.

Note: if a foreign key constraint is declared, you might need to delete from EXAM and SPORT first.