You have any way to normalize that original source table? It's full of extents, which is the first sign of badness in a table design and assured of complicating your life.
Clay RB Smissaert wrote: > 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] > ----------------------------------------------------------------------------- > -- CeaMuS http://www.ceamus.com Simple Content Management ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
