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

    Unanswered: Update issues..........

    I am trying to update the tjugg_final table with the date in the unsub_final table.

    Same table structure but the column unsub in tjugg_final needs to be updated with the data in the column unsub in the table unsub_final.

    update tjugg_final j
    set j.unsub = (select t.unsub from unsub_final t
    where t.market_code = j.market_code
    and t.list_acct_no = j.list_acct_no)
    where (j.market_code, j.list_acct_no) IN
    (select t.market_code, t.unsub from unsub_final t
    where t.market_code = j.market_code
    and t.list_acct_no = j.list_acct_no);
    commit;

    I am getting an ora-01427 and I do have an IN clause in my statement.

    Does anyone know what is wrong?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    01427, 00000, "single-row subquery returns more than one row"
    // *Cause:
    // *Action:
    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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In other words, the problem is here:

    set j.unsub = (select t.unsub from unsub_final t
    where t.market_code = j.market_code
    and t.list_acct_no = j.list_acct_no)

    Apparently (market_code,list_acct_no) do not form a primary or unique key for unsub_final, so you get more than one row back. You can't set j.unsub to more than 1 value at a time!

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you have duplicates in your subquery qwhere you SET the value.
    try this:
    PHP Code:
    update tjugg_final j
        set j
    .unsub = (
            
    select DISTINCT t.unsub /* WHICH unsub do you want?  distinct still might not work */
            
    from unsub_final t 
            where t
    .market_code j.market_code 
            
    and t.list_acct_no j.list_acct_no)
    where (j.market_codej.list_acct_noIN
    (select t.market_codet.unsub 
     from unsub_final t 
     where t
    .market_code j.market_code 
     
    and t.list_acct_no j.list_acct_no); 
    i don't even think you need the bottom where clause do you??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2004
    Posts
    113
    Yes, Duck I do have dups in my subquery.
    The distinct unsub I want is the one in the unsub_final table.

    andrewst,

    you are correct there is not primary key.

    What about if I do a rownum=1?

Posting Permissions

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