Delete using inner join with composite primary key-CodePudding

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

CodePudding user response：

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
  );