Update with embedded select
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Update with embedded select

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    126

    Unhappy

    Hi,
    I have this problem when running the following update statement:

    SQL> UPDATE dwsal_sales_dly b
    2 SET (ret_qty_dor,
    3 ret_trade_allow_amt_dor,
    4 ret_promo_allow_amt_dor)=
    5 (SELECT a.ret_qty ,
    6 a.ret_trade_allow_amt ,
    7 a.ret_promo_allow_amt
    8 FROM dwsal_sales_dly_dor_stage a
    9 WHERE a.db_no = b.db_no
    10 AND a.site_no = b.site_no
    11 AND a.ret_run_no = b.run_no
    12 AND a.cust_no = b.cust_no
    13 AND a.prod_no = b.prod_no
    14 AND a.ret_delv_date = b.delv_date
    15 AND a.ret_delv_date <
    16 (SELECT MIN(delv_date) FROM dwsal_sales_dkt_stage)
    17 )
    18 ;

    18617 rows updated.

    But when I qurey the table - using select statement of the above update :
    SQL> l
    1 select count(*)
    2 from dwsal_sales_dly b ,
    3 dwsal_sales_dly_dor_stage a
    4
    5 WHERE a.db_no = b.db_no
    6 AND a.site_no = b.site_no
    7 AND a.ret_run_no = b.run_no
    8 AND a.cust_no = b.cust_no
    9 AND a.prod_no = b.prod_no
    10 AND a.ret_delv_date = b.delv_date
    11 AND a.ret_delv_date <
    12 (SELECT MIN(delv_date) FROM dwsal_sales_dkt_stage)
    13*
    SQL> /

    COUNT(*)
    ---------
    0

    I receive 0 row.


    Note: table dwsal_sales_dly has 18617 rows.

    Can any one answer this problem?

    Thanks heeps



  2. #2
    Join Date
    Aug 2000
    Posts
    17
    This is a quite common problem.

    At the moment the UPDATE statement updates all rows of the dwsal_sales_dly table with the values provided by your subquery. If your subquery retrieves no data then the columns you are updating (ret_qty_dor, ret_trade_allow_amt_dor, ret_promo_allow_amt_dor) are set to NULL.

    So if you query the dwsal_sales_dly table after the update you will find 18617 rows with NULL values for the 3 columns mentioned above.

    To avoid this behaviour you have to add a WHERE clause to your update statement, excluding the rows you do not intend to change.

    bye

    Roger

  3. #3
    Join Date
    Dec 2000
    Posts
    126

    Wink

    Thank you Roger.

    It works fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width