Delete nested rows from table with orphaned parent id

I have one single table where the parentId column can be 0 for top level items or a number for items belonging to other items. There's no limit to how deep the nesting can go.

id     parentId     title
-------------------------
1      0            Item1
2      1            Item11
3      2            Item22
4      0            Item2
5      4            Item21

What I want is that every time I delete one item, every child and child of a child is deleted, until no item has a parentId of an item that does not exist.

I know I can loop through the records and query DELETE starting from the bottom child but I wonder if this can be done only with a SQLite command

You can redefine the table:

CREATE TABLE tablename (
  `id` INTEGER PRIMARY KEY,
  `parentId` INTEGER,
  `title` VARCHAR(6),
  FOREIGN KEY (`parentId`) REFERENCES tablename(`id`) ON DELETE CASCADE
);

so that the column parentId is a foreign key referencing the column id which must be the PRIMARY KEY of the table or must have a UNIQUE index/constraint.<br/>

The ON DELETE CASCADE action makes sure that when you delete a row all the children rows will also be deleted (also the children of the children rows and so on).<br/>

Also instead of 0 you must set the top level items to null so there is no foreign key constraint violation.<br/>

You must turn on foreign key support because it is off by default and this can be done in SQLiteOpenHelper's onConfigure() method:

@Override
public void onConfigure(SQLiteDatabase db){
    db.setForeignKeyConstraintsEnabled(true);
}

Now when you delete a row from the table all the rows of the levels below the level of the row that you deleted will also be deleted.<br/>

You may have to uninstall the app from the device so that the database is deleted and rerun to recreate the database and the table with the new definition.<br/>

See a demo.

This code will delete every single row that has this "ID" in id column or parentId column:

void deleteItemAndChildren(String ID){
        //Deletes the row that has that ID in id column
        int deletedIdsCount = db.delete(tableName, "id=?", new String[]{ID});

        //Deletes the row that has that ID in parentId column
        int deletedChildrenCount = db.delete(tableName, "parentId=?", new String[]{ID});

        Log.i("CountOfDeletedRows", "was "+ ID +" Deleted: " + (deletedIdsCount>0) + " Count of children deleted: " + deletedChildrenCount );
}

Cheers!