Update with embedded select

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.