SQLite error when executing delete on 2 primary keys

Question

I created a table with 2 primary keys and when I tried to delete few of the records at once, but the whole table gets wiped. Here is the code I tried and I am not sure why it does that. Any help will be most welcomed.

BEGIN TRANSACTION;
DROP TABLE NAMES1;
/* Create a table called NAMES1 */
CREATE TABLE NAMES1(Id integer, Id2 integer, Name text,PRIMARY KEY(Id,Id2));

/* Create few records in this table */
INSERT INTO NAMES1 VALUES(1,1,'Tom');
INSERT INTO NAMES1 VALUES(2,1,'Lucy');
INSERT INTO NAMES1 VALUES(3,4,'Frank');
INSERT INTO NAMES1 VALUES(4,1,'Jane');
INSERT INTO NAMES1 VALUES(5,6,'Robert');
COMMIT;

/* Display all the records from the table */

SELECT Count(*) FROM NAMES1; /* Returns 5 */
DELETE FROM NAMES1 WHERE (Id AND Id2) in ((2 AND 2),(1 AND 1),(2 AND 1));
SELECT Count(*) FROM NAMES1; /* Returns 0 */

By my calculations the returned count should have been 3, not 0.

Answer 1

In SQLite, you cannot compare multiple values with a single expression.

The simplest way to delete multiple records is to do it one by one:

BEGIN;
DELETE FROM Names1 WHERE Id = 2 AND Id2 = 2;
DELETE FROM Names1 WHERE Id = 1 AND Id2 = 1;
DELETE FROM Names1 WHERE Id = 2 AND Id2 = 1;
COMMIT;

Alternatively, write a condition that explicitly checks for all three combinations:

DELETE FROM Names1
WHERE (Id = 2 AND Id2 = 2)
   OR (Id = 1 AND Id2 = 1)
   OR (Id = 2 AND Id2 = 1);

Answer 2

the line:

DELETE FROM NAMES1 WHERE (Id AND Id2) in ((2 AND 2),(1 AND 1),(2 AND 1));

is equivalent to:

DELETE FROM NAMES1 WHERE 1 in ((1,1,1);

and delete the entire table

change it to:

DELETE FROM NAMES1 WHERE Id in (2,1) AND Id2 in (2,1);