> 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.
Yes, I remember trying to do this at first via joins, but I couldn't get that to work and ended up with the current solution. Still, it wouldn't surprise me if there was a better way to do this. Simplified, this is what I am trying to do: Given a table like this: PID DATE CODE ---------------------- 1 20030101 aa 1 20051112 dge 2 19980505 dd 3 20010808 ee 3 20031212 mm I need to transpose this to get only unique PID's in column 1 and the other data grouped in the same row, like this: PID DATE_1 CODE_1 DATE_2 CODE_2 ------------------------------------------------ 1 20030101 aa 20051112 dge 2 19980505 dd 3 20010808 ee 20031212 mm Etc. The number of rows per PID is variable and that can be up to maybe 100. Also the number of different items per row as in the first table is variable and that could be up to maybe 10. RBS -----Original Message----- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 28 January 2007 19:14 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- 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] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
