5 Replies Latest reply: Oct 8, 2013 1:07 PM by jgarry RSS

    Update a table with matching null values

    user7993571

      I need to update a table that is having null values in its two columns and need to update theses null values with not null values from other table

       

      The matching records that subject to be updated is driven from a select statement from another table

       

      Two tables

      1, arf_site_visit

      2  arf_line_item

       

      Need to update  two columns (site_visit_id,session_id) of table arf_line_item based on the below given join query

       

      SELECT v.site_visit_id,

             v.session_id

          FROM

            arf_site_visit v,

            arf_line_item i

          WHERE

            i.submit_day_id >= v.start_day_id

            AND i.submit_day_id <= v.end_day_id

            AND i.submit_time_id >= v.start_time_id

            AND i.submit_time_id <= v.end_time_id

            AND i.session_id IS NULL

       

      Additional info

       

      Table description

       

      arf_site_visit

       

      Name                                      Null?    Type

      ----------------------------------------- -------- ----------------------------

      SITE_VISIT_ID                                      NUMBER(19)                 

      START_TIMESTAMP                                    TIMESTAMP(6)               

      START_DAY_ID                                       VARCHAR2(40)               

      START_TIME_ID                                      NUMBER(10)                 

      END_DAY_ID                                         VARCHAR2(40)               

      END_TIME_ID                                        NUMBER(10)                 

      START_VISIT_TIMESTAMP                              TIMESTAMP(6)               

      SITE_VISIT_START_DAY_ID                            VARCHAR2(40)               

      SITE_VISIT_START_TIME_ID                           NUMBER(10)                 

      SITE_VISIT_END_DAY_ID                              VARCHAR2(40)               

      SITE_VISIT_END_TIME_ID                             NUMBER(10)                 

      VISIT_END_DAY_ID                                   VARCHAR2(40)               

      VISIT_END_TIME_ID                                  NUMBER(10)                 

      VISITOR_ID                                         NUMBER(10)                 

      NVISITOR_ID                                        VARCHAR2(40)               

      STIMGRP_ID                                         NUMBER(10)                 

      DEMOGRAPHIC_ID                                     NUMBER(5)                  

      SITE_ID                                            NUMBER(5)                  

      ENTRY_SITE_ID                                      NUMBER(5)                  

      EXIT_SITE_ID                                       NUMBER(5)                  

      REFERRING_SITE_ID                                  NUMBER(5)                  

      SEQUENCE_NUM                                       NUMBER(3)                  

      USER_AGENT_ID                                      NUMBER(5)                  

      REFERRER_ID                                        NUMBER(5)                  

      SESSION_ID                                         VARCHAR2(128)              

      NUM_PAGE_VIEWS                                     NUMBER(5)                  

      DURATION_SECONDS                                   NUMBER(10)                 

      TOTAL_ELAPSED_DURATION_SECONDS                     NUMBER(10)                 

      SITE_VISIT_DURATION_SECONDS                        NUMBER(10)                 

      RESOURCE_DURATION_SECONDS                          NUMBER(10)  

       

       

      arf_line_item

       

      Name                                      Null?    Type

      ----------------------------------------- -------- ----------------------------

      SUBMIT_TIMESTAMP                                   TIMESTAMP(6)               

      SUBMIT_DAY_ID                                      VARCHAR2(40)               

      SUBMIT_TIME_ID                                     NUMBER(10)                 

      SKU_ID                                             NUMBER(10)                 

      PRODUCT_ID                                         NUMBER(10)                 

      CATEGORY_ID                                        NUMBER(10)                 

      CUSTOMER_ID                                        NUMBER(10)                 

      NCUSTOMER_ID                                       VARCHAR2(40)               

      AGENT_ID                                           VARCHAR2(40)               

      ORIGIN_SALES_CHANNEL_ID                            NUMBER(3)                  

      SUBMIT_SALES_CHANNEL_ID                            NUMBER(3)                  

      STIMGRP_ID                                         NUMBER(10)                 

      SEGCLSTR_ID                                        NUMBER(10)                 

      PROMOGRP_ID                                        NUMBER(5)                  

      BILLING_REGION_ID                                  NUMBER(5)                  

      SHIPPING_REGION_ID                                 NUMBER(5)                  

      LOCAL_CURRENCY_ID                                  NUMBER(5)                  

      DEMOGRAPHIC_ID                                     NUMBER(5)                  

      SITE_VISIT_ID                                      NUMBER(19)                 

      ORDER_ID                                           NUMBER(10)                 

      LINE_ITEM_ID                                       NUMBER(19)

      NORDER_ID                                          VARCHAR2(40)

      NLINE_ITEM_ID                                      VARCHAR2(40)

      SESSION_ID                                         VARCHAR2(128)

      QUESTION_ID                                        NUMBER(10)

      QUANTITY                                           NUMBER(10)

      IS_MARKDOWN                                        NUMBER(1)

      LOCAL_UNIT_PRICE                                   NUMBER(19,7)

      LOCAL_GROSS_REVENUE                                NUMBER(19,7)

      LOCAL_DISCOUNT_AMOUNT                              NUMBER(19,7)

      LOCAL_MARKDOWN_DISC_AMOUNT                         NUMBER(19,7)

      LOCAL_ORDER_TAX_ALLOC                              NUMBER(19,7)

      LOCAL_ORDER_SHIPPING_ALLOC                         NUMBER(19,7)

      LOCAL_ORDER_DISCOUNT_ALLOC                         NUMBER(19,7)

      LOCAL_NET_REVENUE                                  NUMBER(19,7)

      LOCAL_ORDER_NET_REVENUE                            NUMBER(19,7)

      LOCAL_APPSMT_DBT_ALLOC_AMT                         NUMBER(19,7)

      LOCAL_APPSMT_CDT_ALLOC_AMT                         NUMBER(19,7)

      LOCAL_PRICE_OVERRIDE_AMT                           NUMBER(19,7)

      STANDARD_UNIT_PRICE                                NUMBER(19,7)

      STANDARD_GROSS_REVENUE                             NUMBER(19,7)

      STANDARD_DISCOUNT_AMOUNT                           NUMBER(19,7)

      STANDARD_MARKDOWN_DISC_AMOUNT                      NUMBER(19,7)

      STANDARD_ORDER_TAX_ALLOC                           NUMBER(19,7)

      STANDARD_ORDER_SHIPPING_ALLOC                      NUMBER(19,7)

      STANDARD_ORDER_DISCOUNT_ALLOC                      NUMBER(19,7)

      STANDARD_NET_REVENUE                               NUMBER(19,7)

      STANDARD_ORDER_NET_REVENUE                         NUMBER(19,7)

      STANDARD_APPSMT_DBT_ALLOC_AMT                      NUMBER(19,7)

      STANDARD_APPSMT_CDT_ALLOC_AMT                      NUMBER(19,7)

      STANDARD_PRICE_OVERRIDE_AMT                        NUMBER(19,7)

      SUBMITTED_SITE_ID                                  NUMBER(5)

      ORIGIN_SITE_ID                                     NUMBER(5)

      ITEM_SITE_ID                                       NUMBER(5)

      IS_COUPON_APPLIED                                  NUMBER(1)

        • 1. Re: Update a table with matching null values
          DK2010

          Hi,

           

          You can try something like  make sure your  select Query return the row what you want to update

          update arf_line_item i

          set (i.site_visit_id,i.session_id)=(SELECT v.site_visit_id,v.session_id  FROM  arf_site_visit v

               where  i.submit_day_id >= v.start_day_id

               AND i.submit_day_id <= v.end_day_id

                       AND i.submit_time_id >= v.start_time_id

                       AND i.submit_time_id <= v.end_time_id

               AND i.session_id IS NULL)

          /

           

          • 2. Re: Update a table with matching null values
            user7993571

            Thanks for the quick reply

            • 3. Re: Update a table with matching null values
              user7993571

              Hi

              Am getting an error during the execution

               

              ORA-01427: single-row subquery returns more than one row

               

              SQL> update dw.arf_line_item ai

                set

                (ai.session_id)=(

                  SELECT distinct av.session_id FROM

                  DW.arf_site_visit av,

                  DW.dxl_site_visit dv,

                  DW.dxl_line_item di

                  WHERE di.line_item_id = ai.line_item_id

                  AND di.org_id = dv.org_id

                  AND dv.SITE_VISIT_ID = av.SITE_VISIT_ID

                  AND ai.submit_day_id >= av.start_day_id

                  AND ai.submit_day_id <= av.end_day_id

                  AND ai.submit_time_id >= av.start_time_id

                  AND ai.submit_time_id <= av.end_time_id

                  AND ai.session_id IS NULL

                )

                where ai.session_id is null;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

                  SELECT distinct av.session_id FROM

                  *

              ERROR at line 4:

              ORA-01427: single-row subquery returns more than one row

               

               

              Could you please help me on this update

              • 4. Re: Update a table with matching null values
                DK2010

                Hi

                 

                Error is Clear ORA-01427: single-row subquery returns more than one row

                 

                your select  criteria should return single value.  if you wanted to do multiple value,multiple column update you have to use loop for that.

                 

                HTH

                • 5. Re: Update a table with matching null values
                  jgarry

                  When I run into stuff like this and it isn't obvious why there are multiple rows, I'll run the select into two listings, with and without the distinct, and sorted, with no headings.  Then it is simple to diff the two, and the answer usually becomes quite obvious.