I have a table (student_table) with 5 columns in which alphanumeric data was stored earlier in one of the column.The combination was unique. However, due to a change at source, the aplhanumeric values were replaced with numeric values and inserted, keeping rest of the column values same which was still unique. Refer sample data below -
| Student Id | Student Name | Class | Section | Subject |
|---|---|---|---|---|
| 1 | ABC | Secondary | A | Maths |
| 1 | ABC | Secondary | A | 1 |
| 2 | XYZ | Secondary | A | English |
| 2 | XYZ | Secondary | A | 2 |
| 3 | TES | Secondary | A | 2 |
I used the below query but that only give me the records of students having more than one subject.
Select Student_id, Student_name, Class, Section from (Select DISTINCT Student_id, Student_name, Class, Section from student_table where Student_id = 1) having count(subject) > 1 group by Student_id, Student_name, Class, Section;
I want to delete the 2 records where the subject is alphanumeric/string i.e. 'Maths','English' and keep only the 3 rows with numeric values in Subject column. How can I do that? There is no foreign key relationship between this table as there is no master table for subject codes.