Re: [sqlite] Delete from A what is not in B

2010-10-04 Thread Max Vlasov
On Sat, Oct 2, 2010 at 4:02 PM, Igor Tandetnik wrote: > Fadhel Al-Hashim wrote: > > I have two tables that contain about 5 million records. I am trying to > write > > an SQL command to delete rows from table A with PK (x,y,z) where PK > (x,y,z) > > is not in table B. > > > delete from A where ro

Re: [sqlite] Delete from A what is not in B

2010-10-04 Thread Fadhel Al-Hashim
yes PRIMARY KEY (x,y,z) On Sat, Oct 2, 2010 at 9:10 PM, Simon Slavin wrote: > > On 2 Oct 2010, at 1:15pm, Fadhel Al-Hashim wrote: > > > I did not add indices on those columns assuming that being PK is enough? > is > > that right? > > How did you define the primary keys ? Was it PRIMARY KEY (x,

Re: [sqlite] Delete from A what is not in B

2010-10-02 Thread Simon Slavin
On 2 Oct 2010, at 1:15pm, Fadhel Al-Hashim wrote: > I did not add indices on those columns assuming that being PK is enough? is > that right? How did you define the primary keys ? Was it PRIMARY KEY (x,y,z) ? Simon. ___ sqlite-users mailing list sqli

Re: [sqlite] Delete from A what is not in B

2010-10-02 Thread Drake Wilson
Quoth Fadhel Al-Hashim , on 2010-10-02 15:15:29 +0300: > I did not add indices on those columns assuming that being PK is enough? is > that right? Are you saying _both_ tables have that exact set of columns (ideally in the same order, too) as their respective primary keys? A primary key declarati

Re: [sqlite] Delete from A what is not in B

2010-10-02 Thread Fadhel Al-Hashim
I did not add indices on those columns assuming that being PK is enough? is that right? On Sat, Oct 2, 2010 at 3:08 PM, Drake Wilson wrote: > Quoth Fadhel Al-Hashim , on 2010-10-02 14:51:59 +0300: > > tryingA.x<>B.xANDA.y<>B.y ANDA.z<>B.z would just > > hang. > > Huh? How

Re: [sqlite] Delete from A what is not in B

2010-10-02 Thread Drake Wilson
Quoth Fadhel Al-Hashim , on 2010-10-02 14:51:59 +0300: > tryingA.x<>B.xANDA.y<>B.y ANDA.z<>B.z would just > hang. Huh? How are you using that expression? I'd use DELETE FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE a.key = b.key) with a suitable composite equality in the

Re: [sqlite] Delete from A what is not in B

2010-10-02 Thread Igor Tandetnik
Fadhel Al-Hashim wrote: > I have two tables that contain about 5 million records. I am trying to write > an SQL command to delete rows from table A with PK (x,y,z) where PK (x,y,z) > is not in table B. delete from A where not exists (select 1 from B where A.x = B.x and A.y = B.y and A.z = B.z);

[sqlite] Delete from A what is not in B

2010-10-02 Thread Fadhel Al-Hashim
Good day everyone. I have two tables that contain about 5 million records. I am trying to write an SQL command to delete rows from table A with PK (x,y,z) where PK (x,y,z) is not in table B. tried using "NOT IN" but my PK is composite. tryingA.x<>B.xANDA.y<>B.y ANDA.z<>B.z