--- Fred Williams <[EMAIL PROTECTED]> wrote: > 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! :-)
It's not complicated at all. The UPDATE statement in the original example is a way to compensate for SQLite's inability to perform a join on another table before doing an UPDATE. That's why the author needed all those inner selects each returning a single value. http://www.sqlite.org/lang_update.html Had the MySQL UPDATE syntax been available, the same UPDATE statement could be expressed in less than 10% of the SQL in the original SQL statement, with far fewer database accesses due to the reduced number of SQL subqueries. http://dev.mysql.com/doc/refman/5.0/en/update.html Another alternative is for the SQLite queryer to first query all those other tables first and then programmatically (via C or whatever) construct and execute the UPDATE statement. But I can understand why anyone would want to avoid this extra work just to perform a straight-forward UPDATE. > > 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 ____________________________________________________________________________________ Don't get soaked. Take a quick peak at the forecast with the Yahoo! Search weather shortcut. http://tools.search.yahoo.com/shortcuts/#loc_weather ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
