Delete using inner join with composite primary key

Question

I am trying to delete some records from my database, I have succeeded and it works when testing in "DB Browser for SQLite" software but when I try to copy it to my Android Studio project I get this error in my DAO:

'(',')', '.', BETWEEN or IN expected, got ','

This is my code:

@Query("DELETE FROM AsistenciaEstatus\n" +
        "WHERE (id_persona_ae, id_registro_asistencia_ae) IN(\n" +
        "SELECT id_persona_ae, id_registro_asistencia_ae FROM AsistenciaEstatus\n" +
        "INNER JOIN RegistroAsistencia ON AsistenciaEstatus.id_registro_asistencia_ae=RegistroAsistencia._id\n" +
        "WHERE AsistenciaEstatus.id_persona_ae=:idPersona\n" +
        "AND RegistroAsistencia.id_grupo_ra=:idGrupo)")
void borrarTodosLosEstatusDePersonaEnXGrupo(long idPersona, long idGrupo);

The error appears in the comma of the second line

WHERE (id_persona_ae, id_registro_asistencia_ae)

I don't understand what could be the problem and how to solve it

Answer 1

The use of ROW VALUES was introduced in SQLite in version 3.15.0 and this is supported by API Level 26+ in Android.

If your app targets lower API levels you can use EXISTS in your query:

DELETE FROM AsistenciaEstatus AS a 
WHERE a.id_persona_ae = :idPersona
AND EXISTS ( 
  SELECT 1
  FROM RegistroAsistencia AS r 
  WHERE a.id_registro_asistencia_ae = r._id 
    AND r.id_grupo_ra = :idGrupo
);

or, since you filter id_persona_ae with the parameter :idPersona you can check it separately in the WHERE clause and have the IN subquery to return only 1 column:

DELETE FROM AsistenciaEstatus
WHERE id_persona_ae = :idPersona
  AND id_registro_asistencia_ae IN ( 
    SELECT a.id_registro_asistencia_ae 
    FROM AsistenciaEstatus AS a INNER JOIN RegistroAsistencia AS r 
    ON a.id_registro_asistencia_ae = r._id 
    WHERE a.id_persona_ae = :idPersona 
      AND r.id_grupo_ra = :idGrupo
  );