How to delete multiple rows with 2 columns as composite primary key in SQLite?

First, find out which rows you want to delete.The easiest way is with a join:

SELECT *FROM apt_langJOIN (SELECT 42122 AS apt_fk, 'en' AS apt_lang_fk UNION ALL      SELECT 42123          , 'es'                UNION ALL      SELECT 42123          , 'en'                         )USING (apt_fk, apt_lang_fk)

To use this with a DELTE, either check with EXISTS for a match:

DELETE FROM apt_langWHERE NOT EXISTS (SELECT 1                  FROM apt_lang AS a2                  JOIN (SELECT 42122 AS apt_fk, 'en' AS apt_lang_fk UNION ALL                        SELECT 42123          , 'es'                UNION ALL                        SELECT 42123          , 'en'                         )                  USING (apt_fk, apt_lang_fk)                  WHERE apt_fk      = apt_lang.apt_fk                    AND apt_lang_fk = apt_lang.apt_lang_fk)

or get the ROWIDs of the subquery and check against those:

DELETE FROM apt_langWHERE rowid NOT IN (SELECT apt_lang.rowid                    FROM apt_lang                    JOIN (SELECT 42122 AS apt_fk, 'en' AS apt_lang_fk UNION ALL                          SELECT 42123          , 'es'                UNION ALL                          SELECT 42123          , 'en'                         )                    USING (apt_fk, apt_lang_fk))

This should work:

DELETE FROM apt_lang WHERE (apt_fk, apt_lang_fk) NOT IN (VALUES (42122,"en"),(42123,"es"),(42123,"en"))

Yes, it's possible to delete rows from SQLite based on a subquery that builds on multiple columns. This can be done with SQLite's concatenate "||" operator. It might help to show an example.

Setup:

create table a (x,y); insert into a values ('A','B');insert into a values ('A','C');create table b (x,y);insert into b values ('A','C');insert into b values ('A','X');

Show Tables:

select * from a;A|BA|Cselect * from b;A|CA|X

Assuming you want to delete from table a rows where column x and column y don't match with table b, the following select will accomplish that.

delete from a where x||y not in (select a.x||a.y from a,b where a.x=b.x and a.y=b.y);

Result:

select * from a;A|B

Summary

This relies on concatenating several columns into one with the "||" operator. Note, it will work on calculated values too, but it might require casting the values. So, just a few conversions to note with the "||" operator...

 select 9+12|| 'test'; 21 -- Note we lost 'test' select cast(9+12 as text)|| 'test'; 21test -- Good!  'test' is there.