--- Joe Wilson <[EMAIL PROTECTED]> wrote: > I think this technique might be more compact and efficient than the update:
I think my suggestion may result in duplicate rows. If the INSERT or REPLACE doesn't work, you might try: 1. BEGIN 2. dump the joined select into a temp table 3. delete the affected rows from A3Test115_J 4. insert the rows from the temp table into A3Test115_J 5. drop the temp table. 6. COMMIT But it might not be worth the trouble. > insert or replace into A3Test115_J( > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > ... > NUMERIC_VALUE_E15 > ) > select > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > ... > g15.NUMERIC_VALUE > from > A3Test115_J t1, > GROUP_2 g2, > GROUP_3 g3, > ... > GROUP_15 g15 > where > t1.PATIENT_ID = g2.PID > and t1.PATIENT_ID = g3.PID > and t1.PATIENT_ID = g4.PID > and t1.PATIENT_ID = g5.PID > ... > and t1.PATIENT_ID = g15.PID > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > 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 > > > > ____________________________________________________________________________________ > 8:00? 8:25? 8:40? Find a flick in no time > with the Yahoo! Search movie showtime shortcut. > http://tools.search.yahoo.com/shortcuts/#news > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > ____________________________________________________________________________________ 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] -----------------------------------------------------------------------------
