Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: What am I doing wrong in this Update statement

    Hi All,

    Wonder what I'm doing wrong in this update statement.
    The subquery returns the correct no of rows (4085) whereas the whole query when updated returns 3-3.5 times more rows.

    update proddta.f1212 set main = 'FDX'
    where exists
    (select ammin from proddta.f1212
    join proddta.f7055 on amdb=cndb
    join proddta.f9011 a on a.r6tmt=cntmt
    join proddta.f9011 b on b.r6tmt=amtmt
    where amtmt < 4500 and a.r6cnt = 'US' and ammin <> 'DHL' and amdb in ('1111', '2222'));

    (the am% columns comes from the table F1212)

    Thanks in advance for your help to identify.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    if "3-3.5 times more rows" are all rows in F1212, then the behaviour is correct.
    As the subquery returns some rows, it is TRUE for all updated rows.
    If you want to take (correlate the subquery with) values from the updated table, just remove reference to F1212 from the FROM clause of the subquery.
    You may find some examples with sample codes e.g. in this thread: http://www.dbforums.com/oracle/16299...mn-update.html
    (especially examine the last post)

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    Thanks flyboy,

    Your thread helped me resolve the issue.

    Thanks a lot.

Posting Permissions

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