-
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
-
cool
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
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
|