5 Replies Latest reply: Apr 30, 2014 8:09 AM by chris227 RSS

    Complex update statement using MIN and MAX

    user590978

      Hi All,

       

       

       

        how to write update for below scenario:

       

        I have two tables temp_up1 and temp_upemail1

        so i need to update the dm_email_dkey column in temp_up1 with minimum of dm_email_dkey in temp_upemail1 for the email address associated to dm_email_dkey in temp_up1

        after remove the duplicate  

        duplicate remove query :

      {code}

       

      delete from

         temp_up1  tp

      where rowid in

      (select a from

         (

      SELECT cec.rowid a ,row_number()

          over (partition by CEC.DM_CUSTOMER_DKEY,CEC.DM_EMAIL_TEMPLATE_DKEY,

          CEC.DM_CAMP_SEND_DATE ,

          CEC.DATA_SRC,EM.EMAIL_ADDR order by 2 desc) dup

          FROM TEMP_UP1 CEC

          INNER JOIN TEMP_UPEMAIL1 EM

          ON CEC.DM_EMAIL_DKEY = EM.DM_EMAIL_DKEY )

        where dup > 1);

       

       

        Create table scripts:

       

       

      CREATE TABLE  TEMP_UPEMAIL1

      (

        DM_EMAIL_DKEY         NUMBER                  NOT NULL,

        EMAIL_ADDR            VARCHAR2(50 BYTE),

        START_EFFECTIVE_DATE  DATE,

        EMAIL_ADDR_DOMAIN     VARCHAR2(20 BYTE),

        SPAM_FLAG             CHAR(1 BYTE),

        SPAM_DATE             DATE,

        OPTOUT_FLAG           CHAR(1 BYTE),

        OPTOUT_DATE           DATE,

        END_EFFECTIVE_DATE    DATE,

        CURRENT_FLAG          CHAR(1 BYTE),

        DATA_SRC              VARCHAR2(20 BYTE),

        DM_CREATED_DATE       DATE,

        DM_CREATED_BY         VARCHAR2(20 BYTE),

        DM_UPDATED_DATE       DATE,

        DM_UPDATED_BY         VARCHAR2(20 BYTE),

        BOUNCE_FLAG           CHAR(1 BYTE),

        BOUNCE_DATE           DATE

      )

       

      CREATE TABLE TEMP_UP1

      (

        DM_CUST_EMAIL_CAMPAIGN_FKEY     NUMBER        NOT NULL,

        DM_EMAIL_DKEY                   NUMBER        NOT NULL,

        DM_EMAIL_TEMPLATE_DKEY          NUMBER,

        DM_CAMP_SEND_DATE               DATE,

        DM_EMAIL_TYPE_HIER_HKEY         NUMBER,

        DM_EMAIL_CAT_DKEY               NUMBER,

        DM_EMAIL_PURPOSE_DKEY           NUMBER,

        DM_WEB_CAMP_DKEY                NUMBER        NOT NULL,

        DM_EMAIL_SUBJECT_LINE_DKEY      NUMBER,

        DM_CUSTOMER_DKEY                NUMBER(10),

        DM_SEGMENTATION_DKEY            NUMBER(10),

        DM_LIVE_SEGMENTATION_DKEY       NUMBER(10),

        DM_MAIN_DEST_SEGMENT_DKEY       NUMBER,

        DM_CC_CUSTOMER_TRIPS_DKEY       NUMBER,

        JOB_CODE                        VARCHAR2(20 BYTE),

        PE_CODE                         VARCHAR2(20 BYTE),

        TEST_CODE                       VARCHAR2(20 BYTE),

        BOUNCE_FLAG                     CHAR(1 BYTE),

        BOUNCE_DATE                     DATE,

        SPAM_FLAG                       CHAR(1 BYTE),

        SPAM_DATE                       DATE,

        OPTOUT_FLAG                     CHAR(1 BYTE),

        OPTOUT_DATE                     DATE,

        EMAIL_OPEN_FLAG                 CHAR(1 BYTE),

        NO_OF_OPENS                     NUMBER,

        FIRST_OPEN_DATE                 DATE,

        CLICKED_FLAG                    CHAR(1 BYTE),

        NO_OF_CLICKS                    NUMBER,

        FIRST_CLICK_DATE                DATE,

        NO_OF_RESERVATIONS              NUMBER,

        NO_OF_PAX                       NUMBER,

        GROSS_SALES                     NUMBER(19,4),

        GROSS_PROFIT                    NUMBER(19,4),

        CREATED_DATE                    DATE          NOT NULL,

        CREATED_BY                      VARCHAR2(20 BYTE) NOT NULL,

        UPDATED_FLAG                    CHAR(1 BYTE)  NOT NULL,

        UPDATED_DATE                    DATE,

        UPDATED_BY                      VARCHAR2(20 BYTE),

        DATA_SRC                        VARCHAR2(20 BYTE) NOT NULL,

        DM_CREATED_DATE                 DATE          NOT NULL,

        DM_CREATED_BY                   VARCHAR2(20 BYTE) NOT NULL,

        DM_UPDATED_DATE                 DATE,

        DM_UPDATED_BY                   VARCHAR2(20 BYTE),

        DM_EMAIL_DELIVERY_TYPE_DKEY     NUMBER,

        DM_EMAIL_DELIVERY_SUBTYPE_DKEY  NUMBER

      )

       

      SET DEFINE OFF;

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC, DM_CREATED_DATE,

          DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (2564657, 'elizabeth.mccune@gmail.com', TO_DATE('12/18/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'N', TO_DATE('12/20/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'EPSILON', TO_DATE('12/18/2013 07:04:48', 'MM/DD/YYYY HH24:MI:SS'),

          '15591', TO_DATE('12/20/2013 04:45:48', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC, DM_CREATED_DATE,

          DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (2649872, 'elizabeth.mccune@gmail.com', TO_DATE('12/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'N', TO_DATE('01/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'EPSILON', TO_DATE('12/31/2013 05:34:50', 'MM/DD/YYYY HH24:MI:SS'),

          '15591', TO_DATE('01/03/2014 04:45:36', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC, DM_CREATED_DATE,

          DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (2672477, 'elizabeth.mccune@gmail.com', TO_DATE('01/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'Y', TO_DATE('01/10/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'EPSILON', TO_DATE('01/03/2014 04:45:36', 'MM/DD/YYYY HH24:MI:SS'),

          '15591', TO_DATE('01/10/2014 06:00:41', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC, DM_CREATED_DATE,

          DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (2858809, 'elizabeth.mccune@gmail.com', TO_DATE('01/31/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'N', TO_DATE('02/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'EPSILON', TO_DATE('01/31/2014 05:51:43', 'MM/DD/YYYY HH24:MI:SS'),

          '15591', TO_DATE('02/01/2014 08:14:33', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC, DM_CREATED_DATE,

          DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (2970989, 'elizabeth.mccune@gmail.com', TO_DATE('02/17/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'Y', TO_DATE('02/23/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'EPSILON', TO_DATE('02/17/2014 07:28:48', 'MM/DD/YYYY HH24:MI:SS'),

          '15591', TO_DATE('02/23/2014 07:22:44', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC, DM_CREATED_DATE,

          DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (3093042, 'elizabeth.mccune@gmail.com', TO_DATE('03/07/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'N', TO_DATE('03/11/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'EPSILON', TO_DATE('03/07/2014 06:36:48', 'MM/DD/YYYY HH24:MI:SS'),

          '15591', TO_DATE('03/11/2014 07:30:33', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      Insert into UPANWAR.TEMP_UPEMAIL1

         (DM_EMAIL_DKEY, EMAIL_ADDR, START_EFFECTIVE_DATE, EMAIL_ADDR_DOMAIN, SPAM_FLAG,

          OPTOUT_FLAG, OPTOUT_DATE, END_EFFECTIVE_DATE, CURRENT_FLAG, DATA_SRC,

          DM_CREATED_DATE, DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY, BOUNCE_FLAG)

      Values

         (4777362, 'elizabeth.mccune@gmail.com', TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'gmail', 'N',

          'Y', TO_DATE('03/10/2014 19:05:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/11/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N', 'NEOLANE',

          TO_DATE('04/10/2014 05:08:08', 'MM/DD/YYYY HH24:MI:SS'), '15591', TO_DATE('04/11/2014 04:25:42', 'MM/DD/YYYY HH24:MI:SS'), '15591', 'N');

      COMMIT;

       

      SET DEFINE OFF;

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (66827732, 3093042, 37636, TO_DATE('03/10/2014 17:05:11', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 21923, 24912, 54625029,

          12875, 13041, -100, -100, '0360634',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('03/10/2014 19:05:20', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('03/10/2014 19:05:08', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('03/10/2014 19:05:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/11/2014 03:01:16', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/09/2014 02:02:43', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('03/11/2014 07:36:01', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/09/2014 04:59:41', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (60291616, 2564657, 33427, TO_DATE('12/19/2013 16:05:59', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 18917, 21311, 54625029,

          12875, 13041, -100, -100, '0360444',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('12/20/2013 00:50:27', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('12/20/2013 00:50:22', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('12/20/2013 00:50:27', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/20/2013 01:49:05', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/09/2014 02:02:43', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('12/21/2013 05:21:43', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/09/2014 04:59:41', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (61471878, 2649872, 34633, TO_DATE('01/02/2014 18:09:04', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 19323, 22312, 54625029,

          12875, 13041, -100, -100, '0360431',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('01/02/2014 19:55:03', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('01/02/2014 19:54:55', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('01/02/2014 19:55:03', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/03/2014 01:46:06', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/09/2014 02:02:43', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('01/04/2014 05:00:48', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/09/2014 04:59:41', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (61697206, 2672477, 34635, TO_DATE('01/06/2014 16:05:27', 'MM/DD/YYYY HH24:MI:SS'), 239,

          105, 501, 19326, 16602, 54625029,

          12875, 13041, -100, -100, '1662048',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('01/06/2014 16:38:09', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('01/06/2014 16:38:01', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('01/06/2014 16:38:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2014 01:45:11', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/09/2014 02:02:43', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('01/07/2014 05:57:30', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/09/2014 04:59:41', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG, OPTOUT_DATE,

          EMAIL_OPEN_FLAG, NO_OF_OPENS, CLICKED_FLAG, NO_OF_CLICKS, FIRST_CLICK_DATE,

          CREATED_DATE, CREATED_BY, UPDATED_FLAG, UPDATED_DATE, UPDATED_BY,

          DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (63805731, 2858809, 8750, TO_DATE('01/31/2014 11:41:52', 'MM/DD/YYYY HH24:MI:SS'), 207,

          104, 501, 5241, 23624, 54625029,

          12875, 13041, -100, -100, '2401154',

          'A', 'N', 'N', 'Y', TO_DATE('01/31/2014 11:57:33', 'MM/DD/YYYY HH24:MI:SS'),

          'N', 0, 'Y', 1, TO_DATE('01/31/2014 11:57:33', 'MM/DD/YYYY HH24:MI:SS'),

          TO_DATE('02/01/2014 02:17:09', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U', TO_DATE('04/09/2014 02:02:43', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON',

          'EPSILON', TO_DATE('02/01/2014 08:21:41', 'MM/DD/YYYY HH24:MI:SS'), '15569', TO_DATE('04/09/2014 04:59:41', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (65241500, 2970989, 36737, TO_DATE('02/17/2014 12:39:45', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 21032, 24213, 54625029,

          12875, 13041, -100, -100, '0360582',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('02/17/2014 13:22:17', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('02/17/2014 13:22:11', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('02/17/2014 13:22:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/18/2014 01:59:05', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/09/2014 02:02:43', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('02/18/2014 07:30:34', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/09/2014 04:59:41', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (69160573, 4777362, 34633, TO_DATE('01/02/2014 18:09:04', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 19323, 22312, 54625029,

          12875, 13041, -100, -100, '0360431',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('01/02/2014 19:55:03', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('01/02/2014 19:54:55', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('01/02/2014 19:55:03', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/03/2014 01:46:06', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/16/2014 02:01:26', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('04/10/2014 05:15:59', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/16/2014 05:24:45', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG, OPTOUT_DATE,

          EMAIL_OPEN_FLAG, NO_OF_OPENS, CLICKED_FLAG, NO_OF_CLICKS, FIRST_CLICK_DATE,

          CREATED_DATE, CREATED_BY, UPDATED_FLAG, UPDATED_DATE, UPDATED_BY,

          DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY, DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (69159408, 4777362, 8750, TO_DATE('01/31/2014 11:41:52', 'MM/DD/YYYY HH24:MI:SS'), 207,

          104, 501, 5241, 23624, 54625029,

          12875, 13041, -100, -100, '2401154',

          'A', 'N', 'N', 'Y', TO_DATE('01/31/2014 11:57:33', 'MM/DD/YYYY HH24:MI:SS'),

          'N', 0, 'Y', 1, TO_DATE('01/31/2014 11:57:33', 'MM/DD/YYYY HH24:MI:SS'),

          TO_DATE('02/01/2014 02:17:09', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U', TO_DATE('04/16/2014 02:01:26', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON',

          'EPSILON', TO_DATE('04/10/2014 05:15:59', 'MM/DD/YYYY HH24:MI:SS'), '15569', TO_DATE('04/16/2014 05:24:45', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (69161900, 4777362, 33427, TO_DATE('12/19/2013 16:05:59', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 18917, 21311, 54625029,

          12875, 13041, -100, -100, '0360444',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('12/20/2013 00:50:27', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('12/20/2013 00:50:22', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('12/20/2013 00:50:27', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/20/2013 01:49:05', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/16/2014 02:01:26', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('04/10/2014 05:15:59', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/16/2014 05:24:45', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (69166742, 4777362, 36737, TO_DATE('02/17/2014 12:39:45', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 21032, 24213, 54625029,

          12875, 13041, -100, -100, '0360582',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('02/17/2014 13:22:17', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('02/17/2014 13:22:11', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('02/17/2014 13:22:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/18/2014 01:59:05', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/16/2014 02:01:26', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('04/10/2014 05:15:59', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/16/2014 05:24:45', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (69166272, 4777362, 37636, TO_DATE('03/10/2014 17:05:11', 'MM/DD/YYYY HH24:MI:SS'), 266,

          105, 501, 21923, 24912, 54625029,

          12875, 13041, -100, -100, '0360634',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('03/10/2014 19:05:20', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('03/10/2014 19:05:08', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('03/10/2014 19:05:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/11/2014 03:01:16', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/16/2014 02:01:26', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('04/10/2014 05:15:59', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/16/2014 05:24:45', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      Insert into UPANWAR.TEMP_UP1

         (DM_CUST_EMAIL_CAMPAIGN_FKEY, DM_EMAIL_DKEY, DM_EMAIL_TEMPLATE_DKEY, DM_CAMP_SEND_DATE, DM_EMAIL_TYPE_HIER_HKEY,

          DM_EMAIL_CAT_DKEY, DM_EMAIL_PURPOSE_DKEY, DM_WEB_CAMP_DKEY, DM_EMAIL_SUBJECT_LINE_DKEY, DM_CUSTOMER_DKEY,

          DM_SEGMENTATION_DKEY, DM_LIVE_SEGMENTATION_DKEY, DM_MAIN_DEST_SEGMENT_DKEY, DM_CC_CUSTOMER_TRIPS_DKEY, JOB_CODE,

          PE_CODE, TEST_CODE, BOUNCE_FLAG, SPAM_FLAG, OPTOUT_FLAG,

          OPTOUT_DATE, EMAIL_OPEN_FLAG, NO_OF_OPENS, FIRST_OPEN_DATE, CLICKED_FLAG,

          NO_OF_CLICKS, FIRST_CLICK_DATE, CREATED_DATE, CREATED_BY, UPDATED_FLAG,

          UPDATED_DATE, UPDATED_BY, DATA_SRC, DM_CREATED_DATE, DM_CREATED_BY,

          DM_UPDATED_DATE, DM_UPDATED_BY)

      Values

         (69161082, 4777362, 34635, TO_DATE('01/06/2014 16:05:27', 'MM/DD/YYYY HH24:MI:SS'), 239,

          105, 501, 19326, 16602, 54625029,

          12875, 13041, -100, -100, '1662048',

          '0', 'NA', 'N', 'N', 'Y',

          TO_DATE('01/06/2014 16:38:09', 'MM/DD/YYYY HH24:MI:SS'), 'Y', 1, TO_DATE('01/06/2014 16:38:01', 'MM/DD/YYYY HH24:MI:SS'), 'Y',

          1, TO_DATE('01/06/2014 16:38:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2014 01:45:11', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'U',

          TO_DATE('04/16/2014 02:01:26', 'MM/DD/YYYY HH24:MI:SS'), 'EPSILON', 'EPSILON', TO_DATE('04/10/2014 05:15:59', 'MM/DD/YYYY HH24:MI:SS'), '15569',

          TO_DATE('04/16/2014 05:24:45', 'MM/DD/YYYY HH24:MI:SS'), '15569');

      COMMIT;

      {code}


      How i can write the update in single query. I don;'t want to use cursior


      Thanks in advance!


      Thanks,


        • 1. Re: Complex update statement using MIN and MAX
          rp0428

          Please delete your second post. People will respond if, and when, they want to respond.

          I have two tables temp_up1 and temp_upemail1 

            so i need to update the dm_email_dkey column in temp_up1 with minimum of dm_email_dkey in temp_upemail1 for the email address associated to dm_email_dkey in temp_up1

            after remove the duplicate  

          That query doesn't remove 'duplicates'. Neither table has ANY primary key or unique indexes so 'duplicates' would have to include ALL columns in the table.

           

          Edit your post and REMOVE all of the unnecessary statements. Only the MINIMUM amount of sample data us needed; for example one or two rows for each table.

           

          Then it will be easier to see what data you really have to work with.

           

          Also show an example of updating the values you want to update: 1) show a row and values BEFORE an update and then 2) after the update.

          • 2. Re: Complex update statement using MIN and MAX
            IrfanA
            so i need to update the dm_email_dkey column in temp_up1 with minimum of dm_email_dkey in temp_upemail1 for the email address associated to dm_email_dkey in temp_up1

            From your above requirement you may be looking for this ?  If not please explain in more detail about your exact requirement,

             

            UPDATE temp_up1

               SET dm_email_dkey =

                      (SELECT MIN (a.dm_email_dkey)

                         FROM temp_upemail1 a)

            • 3. Re: Complex update statement using MIN and MAX
              user590978

              I Will remove this post and send the updated.

               

              THanks

              • 4. Re: Complex update statement using MIN and MAX
                user590978

                Hey irfan, it's right but i need to update all the records in temp up with min of dm_email_dkey

                Associated with email address in second table .

                i will send you a exam with data set for easy understanding

                 

                thanks

                • 5. Re: Complex update statement using MIN and MAX
                  chris227

                   

                  UPDATE temp_up1 u

                  SET dm_email_dkey = (

                    SELECT a.me

                    from (

                      Select

                        MIN (dm_email_dkey) over (partition by EMAIL_ADDR) me

                       ,dm_email_dkey k

                      FROM temp_upemail1) a

                    where

                    a.k = u.dm_email_dkey

                  )