How do i delete these rows in SQLite database?-CodePudding

I have a table in a SQLite database as below:

 ------- ------------ --------------- ------- ------------ 
| ROWID | student_id | qualification | grade | date_stamp |
 ------- ------------ --------------- ------- ------------ 
|     1 |     000001 | Mathematics   | A     | 2022-04-01 |
|     2 |     000002 | NULL          | NULL  | 2022-03-01 |
|     3 |     000003 | Physics       | B     | 2022-03-01 |
|     4 |     000003 | NULL          | NULL  | 2022-02-01 |
 ------- ------------ --------------- ------- ------------

It is a table of student exam results, if a student has a qualification in a subject it appears in the table as ROW #1. If a student has no qualifications it appears in the table as ROW #2.

ROW #3 & #4 refer to a student (id 000003) who previously had no qualifications in the database, but now has a B in Physics. I need to delete ROW #4 based on the fact that this now has a qualification and the NULL values are no longer appropriate. ROW #2 for student 000002 should be unaffected.

The date_stamp column just shows when that record was last updated.

Appreciate any help, thanks in advance.

CodePudding user response：

You may try doing a delete with exists logic:

DELETE
FROM yourTable
WHERE qualification IS NULL AND
      EXISTS (
          SELECT 1
          FROM yourTable t
          WHERE t.student_id = yourTable.student_id AND
                t.qualification IS NOT NULL AND
                t.date_stamp > yourTable.date_stamp
      );