This discussion is archived
5 Replies Latest reply: Oct 8, 2013 11:07 AM by jgarry RSS

Update a table with matching null values

user7993571 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thanks for the quick reply

  • 3. Re: Update a table with matching null values
    user7993571 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points