CODEWITHSUNDEEP
sqlitedelete-rowcomposite-primary-keysql-delete
Ramon Carreras
Ramon Carreras

Reputation: 256

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

I need to delete some rows in a SQLite table with two columns as primary key, like this:

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

This works on Oracle and MySQL but not in SQLite.

Can anybody help me?

Upvotes: 2

Views: 2326

Answers (3)

user13126540
user13126540

Reputation: 31

This should work: 

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

Upvotes: 3

Mike Chirico
Mike Chirico

Reputation: 3491

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|B
A|C

select * from b;
A|C
A|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.

Upvotes: 0

CL.
CL.

Reputation: 180200

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

SELECT *
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)

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

DELETE FROM apt_lang
WHERE 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_lang
WHERE 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))

Upvotes: 4

Related Questions