0

I have SQLite 2 table: Columns: id, parent_id, value

1,0,'TOP FIRST' <---- top category "FIRST"
2,1,'11111-A'   <----subcategory of id 1 (FIRST)
3,1,'11111-B'   <----subcategory of id 1
4,0,'TOP TWO'   <---- top category "TWO"
5,4,'22222-A'   <----subcategory of id 4 (TWO)
6,4,'22222-B'   <----subcategory of id 4
7,100,'to remove'   <----- ORPHAN (There is no top category with id=100)
8,100,'to remove'   <----- ORPHAN (There is no top category with id=100)
9,4,'22222-C'   <----subcategory of id 4 (TWO)

How to remove orphans (id: 7, 8) from this table?

CL.
  • 173,858
  • 17
  • 217
  • 259
pw_1
  • 1
  • 1

1 Answers1

1

You want to delete all rows for which no parent exists. This can be done with a correlated subquery:

DELETE FROM MyTable
WHERE NOT EXISTS (SELECT 1
                  FROM MyTable AS T2
                  WHERE T2.id = MyTable.parent_id);
CL.
  • 173,858
  • 17
  • 217
  • 259
  • It gives an error: SQL logic error or missing database near "SELECT": syntax error – pw_1 Jan 19 '17 at 18:09
  • It works in SQLite 3. In SQLite 2 this does not works. I tried to use PHP (5.3.1) with SQLite 3 but it works only with SQLite 2. – pw_1 Jan 19 '17 at 19:26
  • If you really want to use SQLite 2, you have to implement this manually: read all rows; for each one, execute the subquery; and then delete the row or not. – CL. Jan 19 '17 at 19:58
  • Alone subquery also does not work (in SQLite 2). I would prefer to use SQLite 3 but then: PHP: Fatal error: Uncaught exception 'SQLiteException' with message 'SQLiteDatabase::__construct() [sqlitedatabase.--construct]: file is encrypted or is not a database'. – pw_1 Jan 20 '17 at 06:56
  • You'd have to convert the database file to SQLite 3 first. – CL. Jan 20 '17 at 07:57
  • I did it: Fatal error: Class 'SQLiteDatabase' not found. – pw_1 Jan 20 '17 at 12:25