Deleting Value Using SQLite While Doing an Inner Join

Deleting value using SQlite while doing an INNER JOIN

You can phrase this as a delete with a where clause:

delete from voters
    where votes.party not in ('democrat', 'republican') and
          voters.id in (select id from votes group by id having count(*) = 1);

How delete table inner join with other table in Sqlite?

Try to rewrite you query using subquery: In case your PK for TR_ContactResultRecord is CaseNo

DELETE FROM TR_ContactResultRecord
WHERE CaseNo IN (
  SELECT CaseNo FROM TR_ContactResultRecord a
  INNER JOIN TR_Case b
    ON (a.FireStationCode=b.FireStationCode and a.CaseNo=b.CaseCode )
  WHERE b.Update_DateTime <=20140628134416
);

Deleting rows from two tables using inner join SQLITE

In SQLite, one DELETE command deletes from only one table.

Your query, as written, doesn't actually restrict the records to be deleted, so if you really want to delete all records, you would use this:

DELETE FROM Holiday;
DELETE FROM Accommodation;

If you want to delete one record in the master table and all corresponding records in the child table, you just filter by that key value:

DELETE FROM Holiday       WHERE LocationID = 1;
DELETE FROM Accommodation WHERE LocationID = 1;

Delete using inner join with composite primary key

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

Delete from a SQL Table using a couple JOINs

try like below

delete from Tags
 where user_id in
( select user_id from Albums
  JOIN PICTURES
  ON PICTURES.album_id = Albums.id  
  WHERE Pictures.name LIKE '%pic_name.png%'
)

2nd query

DELETE FROM Pictures
WHERE Pictures.name LIKE '%pic_name.png%'

and 3rd query only need change where

WHERE Users.name ='user1' and Pictures.name= 'pic_name2.png'

Sqlite DELETE trigger with foreign key

You can use NOT EXISTS to check if there is no other row left in IncomeRecordWithTypeValue with the incomeRecordId of the deleted row:

CREATE TRIGGER IF NOT EXISTS Trigger_IncomeTypeIncomeRecordDelete 
AFTER DELETE ON IncomeRecordWithTypeValue  
BEGIN 
  DELETE FROM IncomeRecord   
  WHERE id = OLD.incomeRecordId 
    AND NOT EXISTS(SELECT 1 FROM IncomeRecordWithTypeValue tv WHERE tv.incomeRecordId = IncomeRecord.id); 
END;

SQL DELETE with INNER JOIN

Add .* to s in your first line.

Try:

DELETE s.* FROM spawnlist s
INNER JOIN npc n ON s.npc_templateid = n.idTemplate
WHERE (n.type = "monster");

Delete... Left Join query in sqlite

A simple way is based on an a in clause and subselect with left join

db2.Execute( "DELETE  FROM Word WHERE id in (  
                SELECT id FROM Word as W  
                LEFT JOIN WordSource AS ws ON ws.WordId = w.WordId 
                WHERE ws.WordId IS NULL
              )");

SQLITE - Delete rows with self join?

Don't forget the FROM.

DELETE command

DELETE FROM Expression 
WHERE EXISTS (
    SELECT 1 
    FROM Expression Exp2 
    WHERE Expression.Value=Exp2.Value 
    AND Expression.Id < Exp2.Id
);

---

Related Topics