CODEWITHSUNDEEP
sqlitedelete-row
Nasir
Nasir

Reputation: 750

Delete Rows based on another table with key having multiple columns

There are many queries regarding deleting table records based on another table. But non of them did not work for my scenario.

I am using sqlite3

I have 2 tables with same name in 2 dbs.

DB1:

CREATE TABLE OwnerService1Table1 ("key1" TEXT,"key2" TEXT, "nonkey1" TEXT,"nonkey2" TEXT,PRIMARY KEY("key1","key2"));

Records

Key1    Key2    NonKey1 NonKey2
-------------------------------
AA1     BB1         CC1     DD1
AA2     BB2         CC2     DD2

DB2:

CREATE TABLE OwnerService1Table1 ("key1" TEXT,"key2" TEXT, "key3" TEXT default "KDEF","nonkey1" TEXT,"nonkey2" TEXT,PRIMARY KEY("key1","key2","key3"));

Records

Key1    Key2  Key3    NonKey1 Nonkey2
------------------------------------
AA1     BB1   New1    CC12    DD12
AA3     BB3   New2    CC3     DD3
AA4     BB4   New3    CC4     DD4

(I use attach, open DB2 and attach DB1)

I want to delete all the records from DB2.OwnerService1Table1 where its Key1 and Key2 same with DB1.OwnerService1Table1

In this case row with (AA1,BB1) should be deleted from DB2.OwnerService1Table1

Upvotes: 3

Views: 2553

Answers (1)

Michael Butscher
Michael Butscher

Reputation: 10959

I assume you mean to delete records from DB2.OwnerService1Table1 where its Key1 and Key2 exist (in this combination) in DB1.OwnerService1Table1.

For simplicity I will call the two tables just db1 and db2 here:

The statement is then

delete from db2 where exists (select 1 from db1 where
    db1.key1 = db2.key1 and db1.key2 = db2.key2)

Upvotes: 5

Related Questions