[sqlite] Maintaining Master-Child relationships
Hi, I've two tables in my database, a master and a child with ID as a common key. I've created a table with one column to generate the sequence number.How can I insert related records into both the tables using same ID? Thanks Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] RE: Maintaining Master-Child relationships
Can anyone please look into my query, I've tables Master ( id integer primary key, field1 text) And Child (id integer, name text) My application receive values for field1 and name. I need to populate master and child with incoming values using the same id. A quick reply will be highly appreciated. Regards Ajay -Original Message- From: Arora, Ajay Sent: 28 March 2007 15:04 To: 'sqlite-users@sqlite.org' Subject: Maintaining Master-Child relationships Hi, I've two tables in my database, a master and a child with ID as a common key. I've created a table with one column to generate the sequence number.How can I insert related records into both the tables using same ID? Thanks Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Any way to do this faster?
I'm not sure if SQLite support this syntax, but try following statement, Delete from tableB b Where not exist ( select 'x' from tableA a where a.id = b.id ) -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 26 March 2007 16:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to do this faster? RB Smissaert wrote: Simplified I have the following situation: 2 tables, tableA and tableB both with an integer field, called ID, holding unique integer numbers in tableA and non-unique integer numbers in tableB. Both tables have an index on this field and for tableA this is an INTEGER PRIMARY KEY. Now I need to delete the rows in tableB where this number doesn't appear in the corresponding field in tableA. Currently I do this with this SQL: Delete from tableB where ID not in (select tableA.ID from tableA) When table tableB gets big (say some 10 rows) this will get a bit slow and I wonder if there is a better way to do this. RBS - To unsubscribe, send email to [EMAIL PROTECTED] - Your query is doing a complete table scan of tableA for each record in a table scan of tableB. SQLite version 3.3.13 Enter .help for instructions sqlite create table tableA(id integer primary key, b); sqlite create table tableB(id, c); sqlite create index b_id on tableB(id); sqlite explain query plan delete from tableB where id not in (select tableA.id from tableA); 0|0|TABLE tableB 0|0|TABLE tableA You can improve this greatly using correlated subquery that will use the primary key index on tableA to find any matching records. sqlite explain query plan delete from tableB where not exists (select id from t ableA where tableA.id = tableB.id); 0|0|TABLE tableB 0|0|TABLE tableA USING PRIMARY KEY Note that your index on tableB.id is not used and could be eliminated unless it serves another purpose. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query Issue
Hi, I'm trying to run following query in sqllite, select a.extract_sequence,a.file_location,a.active,b.start_date,b.end_date,b.po rtfolio_code,c.status from extract_master a, extract_parameter b, ( select extract_sequence,status,user_id from extract_status e where datetime = (select max(datetime) from extract_status s where e.extract_sequence = s.extract_sequence) ) c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence And it gives me an error saying e.extract_sequence does not exist. Then I tried writing the same query using a different syntax, select a.extract_sequence,a.file_location,a.active,b.start_date,b.end_date,b.po rtfolio_code,c.status from extract_master a, extract_parameter b, ( select extract_sequence,status,user from extract_status e where (extract_sequence,datetime) in (select extract_sequence,max(datetime) from extract_status group by extract_sequence ) ) c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence But, unfortunately this does not work either and gives an error [syntax error near , ] in the above line. Please let me know if there is anyother way to achieve this in SQLite. Regards Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
