Question about deleting query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Question about deleting query

  1. #1
    Join Date
    Jun 2001
    Posts
    1

    Unhappy

    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

  2. #2
    Join Date
    May 2001
    Posts
    11

    Cool 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
  •  



Click Here to Expand Forum to Full Width