SQL & PL/SQL

hi

i seek help of an expert here.

i have a sql question, pardon me please if i am posting in a wrong group,

i have 2 tables used in the update query (cont and delivery_order_detail)

i want to update column dod_container_ref_no of table delivery_order_detail with cno column of table cont for all the matching records of the 2 tables.

i tried to achieve with 3 queries, but failed...please somebody have a look and correct with the correct query.

Query 1; returned zero rows

SQL> update  delivery_order_detail d

  2  set     dod_prod_container_ref = (

  3  select  to_char(cno)

  4  from    cont c

  5  where   d.dod_doh_no = c.dno

  6  and     c.dpcode = d.dod_prod_code

  7  and     to_char(c.dexpdt,'dd/mm/yyyy') = to_char(d.dod_prod_expiry_date,'dd/mm/yyyy')

  8  )

  9  where d.dod_doh_no = (select x.dno from cont x

10                        where  d.dod_doh_no = x.dno

11                        and    x.dpcode = d.dod_prod_code)

12  /

0 rows updated.

Query 2: returned 25742 rows

Note: there are altogether 25000+ rows in delivery_order_detail table, but i want to replace just 1745 rows of the cont table.

SQL> update  delivery_order_detail d

  2  set     dod_prod_container_ref = (

  3  select  to_char(cno)

  4  from    cont c

  5  where   d.dod_doh_no = c.dno

  6  and     c.dpcode = d.dod_prod_code

  7  and     to_char(c.dexpdt,'dd/mm/yyyy') = to_char(d.dod_prod_expiry_date,'dd/mm/yyyy')

  8  )

  9  /

25742 rows updated.

Query 3:

returned few rows (2029) but when i saw the result of the update in delivery_order_detail table, query returned just 88 rows, which i expected actual 1745 records from cont table.

SQL> ed

Wrote file afiedt.buf

  1  update  delivery_order_detail d

  2  set     dod_prod_container_ref = (

  3  select  to_char(cno)

  4  from    cont c

  5  where   d.dod_doh_no = c.dno

  6  and     c.dpcode = d.dod_prod_code

  7  and     to_char(c.dexpdt,'dd/mm/yyyy') = to_char(d.dod_prod_expiry_date,'dd/mm/yyyy')

  8  )

  9* where d.dod_doh_no in (select x.dno from cont x )

SQL> /

2029 rows updated.

SQL> commit;

Commit complete.

SQL> select count(*) from delivery_order_detail

  2  where dod_prod_container_ref is not null;

COUNT(*)

---------

       88

SQL>