Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: It is me again2.......different issue....i promise

    okay,




    insert into flags
    select j.*
    from zero j, tlisting@ffqa o
    where j.list_acct_no = o.list_acct_no
    and j.market_code = o.market_code
    and o.ad_flag = 'Y';

    40 rows

    select count(*) from flags;

    40 rows

    DELETE tjuggernaut j
    select * from tjuggernaut j, flags o
    where j.list_acct_no = o.list_acct_no
    and j.market_code = o.market_code);

    66069 rows deleted

    Why is it when it goes to delete it deletes all the values in tjuggernaut and I just need it to delete the 40 rows from flags?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You do not have any WHERE clause on the DELETE statement to limit which rows get removed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    haven't we been through this before with an update statement?
    you don't even really need a join here
    PHP Code:
    DELETE tjuggernaut j
    WHERE 
    (j.list_acct_noj.market_codeIN (
    select o.list_acct_noo.market_code
     from flags o
    ); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2004
    Posts
    113
    Anacedent,

    Thank you for your response.


    The Duck,

    Thank you again for your response and yes you are right we did do this with an update statement and I realized that once you brought it to my attention.

    I do have a question though, how do I know whether I need a join.
    I am thinking since I need data from 2 tables, I need a join.
    Is this correct?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can only delete from one table at a time. So if you have to use data in another table to decide if a row goes, then you must include everything in the where clause and reference the second table in a sub-select.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by newbie2004
    Anacedent,
    I do have a question though, how do I know whether I need a join.
    I am thinking since I need data from 2 tables, I need a join.
    Is this correct?
    it depends ...
    for your delete, all you are concerned about is if those two column values EXIST in the other table. no join is necessary since the existence is all that matters (this could be done with an IN or EXISTS statement)
    IF you just wanted to delete ONE record, then possible a join would work, but you could still use a subquery easily.

    in your update you need to update specific rows with values from another table. In order to specify what the value should be a join is necessary to get the proper value. Otherwise you would probably get an error like "subquery returns more than one value". Oracle can't update one row with more than one value.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •