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