Question about deleting query

[chens]

Hi, all:
I am a newbie in SQL*Plus field. I have question about the deleting query that I generated. The following message is the detail description:

I have two tables - table_a, table_b as below:

table_a

Record_ID Record_Name
--------------------------------------------------------
0001 ComCo1
0002 TACO2

table_b

Record_ID Submit_Date Approver_ID Approver_Name
------------------------------------------------------------------------
0001 05-FEB-01 78 Lisa Thompson
0002 06-JUN-01 33 Jenny Wilson
0001 20-FEB-01 45 Ben Allen
0001 01-MAR-01 66 David Billy

I want to create a backup file to store the data which record_name is start on 'C' and the submit_date is older than 3 months. Thus, I generate the select query as below:

select b.record_id, b.submit_date
from table_a a, table_b b
where a.record_id = b.record_id
and a.record_name like 'C%'
and b.record_id in
(select c.record_id from table_b c
where c.record_id = b.record_id
and months_between(sysdate, c.submitl_date) > 3
and c.submit_date in
(select max(d.submit_date) from talbe_b d
where d.record_id = c.record_id)
)

the result of this select query would be:

Record_ID Submit_Date
------------------------------
0001 05-FEB-01
0001 20-FEB-01
0001 01-MAR-01

Now, I want to delete the same data from the table_b, so I generate the delete query as below:

delete from table_b b
where b.record_id =
(select a.record_id from table_a a
where a.record_id = b.record_id)
and a.record_name like = 'C%'
and a.record_id in
(select c.record_id from table_b c
where c.record_id = a.record_id
and months_between(sysdate, c.submit_date) > 3
and c.submit_date =
(select max(d.submit_date) from talbe_b d
where d.record_id = c.record_id)))

I expect the result of the above delete query should delete all the record_id '0001' from table_b. However, I got the other message display:

SQL> start delete_data.sql
Input truncated to 1 characters

0 rows deleted.

Could anyone help me to figure out what's wrong on my delete query? I did try to comment out each where clause to debug which clause case the problem. However, I still got the same result expect I use "delete from table_b" statement. It deletes all the data on table_b.

Thank you for your help...........

Shirley

[sarath]

hi Shirley,
try the following sql statment it will fulfill ur need

delete from table_b b
where b.record_id =
(select a.record_id from table_a a
where a.record_id = b.record_id
and a.record_name like 'C%'
and a.record_id in
(select c.record_id from table_b c
where c.record_id = a.record_id
and months_between (sysdate, c.submit_date) > 3
and c.submit_date =
(select max(d.submit_date) from table_b d
where d.record_id = c.record_id) ));

sarath