Yes, I agree it looks messy, but I used to do this in steps and after advice I think from Igor Tandenik I lumped it all together and run it in one go, which is a lot faster.
RBS -----Original Message----- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 18:49 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Wow! Talk about obfuscated code! I didn't even try to dig deeper than a quick scan, but could this abomination be broken into multiple update queries? On the surface it looks like each "group" is unique. If so, wouldn't a transaction with multiple update statements be much more efficient and a much lighter load on resources on a step by step basis? I damn well know it would be much more pleasing to the eye! :-) Fred > -----Original Message----- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 28, 2007 12:07 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Limit statement size? > > > Is there any limit on the size of the SQL statements in SQLite? > Didn't think this would come into play, but have now come across this > query and wonder if this needs considering: > > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM > GROUP_2 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE > FROM GROUP_2 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE > FROM GROUP_3 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT > NUMERIC_VALUE FROM > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT > ENTRY_ID FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT > READ_CODE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT > TERM_TEXT FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT > START_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT > ADDED_DATE FROM > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 = > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > READ_CODE_E5 = > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > TERM_TEXT_E5 = > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > START_DATE_E5 = > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > ADDED_DATE_E5 = > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE > PATIENT_ID = > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE > PATIENT_ID = > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T > WHERE PATIENT_ID = > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T > WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM > GROUP_7 T WHERE > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE > FROM GROUP_7 T > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE > FROM GROUP_8 T > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT > NUMERIC_VALUE FROM > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT > ENTRY_ID FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT > READ_CODE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT > TERM_TEXT FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT > START_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT > ADDED_DATE FROM > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT > NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 = > (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), > READ_CODE_E10 = > (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), > TERM_TEXT_E10 = > (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), > START_DATE_E10 > = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), > ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID = > T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM > GROUP_10 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E11 = (SELECT ENTRY_ID FROM > GROUP_11 T WHERE > PATIENT_ID = T.PID), READ_CODE_E11 = (SELECT READ_CODE FROM > GROUP_11 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E11 = (SELECT TERM_TEXT FROM > GROUP_11 T WHERE > PATIENT_ID = T.PID), START_DATE_E11 = (SELECT START_DATE FROM > GROUP_11 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E11 = (SELECT > ADDED_DATE FROM GROUP_11 > T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E11 = (SELECT > NUMERIC_VALUE FROM > GROUP_11 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E12 = (SELECT > ENTRY_ID FROM > GROUP_12 T WHERE PATIENT_ID = T.PID), READ_CODE_E12 = (SELECT > READ_CODE FROM > GROUP_12 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E12 = (SELECT > TERM_TEXT FROM > GROUP_12 T WHERE PATIENT_ID = T.PID), START_DATE_E12 = > (SELECT START_DATE > FROM GROUP_12 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E12 = (SELECT > ADDED_DATE FROM GROUP_12 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E12 = > (SELECT NUMERIC_VALUE FROM GROUP_12 T WHERE PATIENT_ID = T.PID), > ENTRY_ID_E13 = (SELECT ENTRY_ID FROM GROUP_13 T WHERE > PATIENT_ID = T.PID), > READ_CODE_E13 = (SELECT READ_CODE FROM GROUP_13 T WHERE > PATIENT_ID = T.PID), > TERM_TEXT_E13 = (SELECT TERM_TEXT FROM GROUP_13 T WHERE > PATIENT_ID = T.PID), > START_DATE_E13 = (SELECT START_DATE FROM GROUP_13 T WHERE PATIENT_ID = > T.PID), ADDED_DATE_E13 = (SELECT ADDED_DATE FROM GROUP_13 T > WHERE PATIENT_ID > = T.PID), NUMERIC_VALUE_E13 = (SELECT NUMERIC_VALUE FROM > GROUP_13 T WHERE > PATIENT_ID = T.PID), ENTRY_ID_E14 = (SELECT ENTRY_ID FROM > GROUP_14 T WHERE > PATIENT_ID = T.PID), READ_CODE_E14 = (SELECT READ_CODE FROM > GROUP_14 T WHERE > PATIENT_ID = T.PID), TERM_TEXT_E14 = (SELECT TERM_TEXT FROM > GROUP_14 T WHERE > PATIENT_ID = T.PID), START_DATE_E14 = (SELECT START_DATE FROM > GROUP_14 T > WHERE PATIENT_ID = T.PID), ADDED_DATE_E14 = (SELECT > ADDED_DATE FROM GROUP_14 > T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E14 = (SELECT > NUMERIC_VALUE FROM > GROUP_14 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E15 = (SELECT > ENTRY_ID FROM > GROUP_15 T WHERE PATIENT_ID = T.PID), READ_CODE_E15 = (SELECT > READ_CODE FROM > GROUP_15 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E15 = (SELECT > TERM_TEXT FROM > GROUP_15 T WHERE PATIENT_ID = T.PID), START_DATE_E15 = > (SELECT START_DATE > FROM GROUP_15 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E15 = (SELECT > ADDED_DATE FROM GROUP_15 T WHERE PATIENT_ID = T.PID), > NUMERIC_VALUE_E15 = > (SELECT NUMERIC_VALUE FROM GROUP_15 T WHERE PATIENT_ID = T.PID) > > > RBS > > > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
