I can see now what the trouble is if I do the SELECT without the INSERT OR REPLACE (shortened):
select t1.PATIENT_ID, g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, g2.START_DATE, g2.ADDED_DATE, g2.NUMERIC_VALUE, g3.ENTRY_ID, g3.READ_CODE, g3.TERM_TEXT, g3.START_DATE, g3.ADDED_DATE, g3.NUMERIC_VALUE, g4.ENTRY_ID, g4.READ_CODE, g4.TERM_TEXT, g4.START_DATE, g4.ADDED_DATE, g4.NUMERIC_VALUE from A3TestB67_J t1, GROUP_2 g2, GROUP_3 g3, GROUP_4 g4 where t1.PATIENT_ID = g2.PID and t1.PATIENT_ID = g3.PID and t1.PATIENT_ID = g4.PID I only get the rows that have entries in all groups. So, this is like an inner join and I need a left join. Have tried this, but it didn't alter the table, although there was no error: INSERT OR REPLACE INTO A3TestB67_J(PATIENT_ID, ENTRY_ID_E1, READ_CODE_E1, TERM_TEXT_E1, START_DATE_E1, ADDED_DATE_E1, NUMERIC_VALUE_E1, ENTRY_ID_E2, READ_CODE_E2, TERM_TEXT_E2, START_DATE_E2, ADDED_DATE_E2, NUMERIC_VALUE_E2, ENTRY_ID_E3, READ_CODE_E3, TERM_TEXT_E3, START_DATE_E3, ADDED_DATE_E3, NUMERIC_VALUE_E3, ENTRY_ID_E4, READ_CODE_E4, TERM_TEXT_E4, START_DATE_E4, ADDED_DATE_E4, NUMERIC_VALUE_E4) select t1.PATIENT_ID, g2.ENTRY_ID, g2.READ_CODE, g2.TERM_TEXT, g2.START_DATE, g2.ADDED_DATE, g2.NUMERIC_VALUE, g3.ENTRY_ID, g3.READ_CODE, g3.TERM_TEXT, g3.START_DATE, g3.ADDED_DATE, g3.NUMERIC_VALUE, g4.ENTRY_ID, g4.READ_CODE, g4.TERM_TEXT, g4.START_DATE, g4.ADDED_DATE, g4.NUMERIC_VALUE from A3TestB67_J t1 left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) My old method is actually quite fast and not sure if I can improve on it. RBS -----Original Message----- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 30 January 2007 05:53 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks, that is how I understood it to be. > I must be overlooking something simple here. Check your SELECT sub-statement within the REPLACE statement to see what rows it returns. .header on .mode tabs create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); insert into t1 values(3, 30,31, 23,230); insert into t1 values(4, 40,41, 24,240); insert into t1 values(5, 50,51, 25,250); create table e2(id primary key, a, b); insert into e2 values(3, 300, 310); insert into e2 values(4, 400, 410); insert into e2 values(5, 500, 510); create table e3(id primary key, a, b); insert into e3 values(3, 23.1, 230.1); insert into e3 values(4, 24.1, 240.1); insert into e3 values(5, 25.1, 250.1); select * from t1 order by id; replace into t1(id, e2_a, e2_b, e3_a, e3_b) select t1.id, e2.a, e2.b, e3.a, e3.b from t1, e2, e3 where t1.id = e2.id and t1.id = e3.id; select * from t1 order by id; id e2_a e2_b e3_a e3_b 3 30 31 23 230 4 40 41 24 240 5 50 51 25 250 id e2_a e2_b e3_a e3_b 3 300 310 23.1 230.1 4 400 410 24.1 240.1 5 500 510 25.1 250.1 > > RBS > > -----Original Message----- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: 29 January 2007 23:52 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Limit statement size? > > RB Smissaert wrote: > > Had a go at this, but sofar I haven't been able yet to get it to work. > > I get no error, but A3Test115_J remains just at it is. > > I couldn't find much information about INSERT OR REPLACE in the SQLite > > documentation. What exactly should it do? > > It will try to do an INSERT. If the PATIENT_ID field is already in the > file, it will delete the old conflicting entry before inserting. > > The best write-up is at: > > http://sqlite.org/lang_conflict.html > > > HTH, > > Gerry ____________________________________________________________________________ ________ No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
