-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|