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

    Unanswered: Here we go again....

    I fixed this query the other day thanks to The Duck but it has been running since last night and this table is not that big.


    update tjuggernaut j
    set j.tv_mth = (select t.tv from tv 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.list_acct_no from tv t
    where t.market_code = j.market_code
    and t.list_acct_no = j.list_acct_no);

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You can try using an UPDATE join like this:

    Code:
    UPDATE
      (SELECT j.tv_mth jtv_mth, t.tv ttv
         FROM tv t, tjuggernaut j
       WHERE t.market_code = j.market_code
           AND t.list_acct_no = j.list_acct_no)
         SET jtv_mth = ttv

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    I wouldn't recommend dropping the where clause - it lead to problems.

    Try
    update tjuggernaut j
    set j.tv_mth = (select t.tv from tv t
    where t.market_code = j.market_code
    and t.list_acct_no = j.list_acct_no)
    where exists (select null from tv t
    where t.market_code = j.market_code
    and t.list_acct_no = j.list_acct_no);

    You where clause was both an IN subquery and a correlated subquery at the same time - this gives the optimizer too many choices and can lead to bad performance.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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