Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: update with select

    Hi,

    I have 2 tables zone,price.

    These 2 tables have in common code & line coloumns

    My work is to update the amt field of zone table with the the condition in below select query.


    select zone.code FROM price, zone where zone.code=price.code and zone.line=price.line and zone.region='126' and zone.area='03' and zone.flag='A' and price.flag='A');
    Above query is returning 60 rows from my DB.

    but when I use this query as part of below query to update the amt in zone table, it is updating 3950 rows, where it should update 60 rows.

    update zone set amt = amt+10 where zone.code in (select zone.code FROM price, zone where zone.code=price.code and zone.prod_line=price.prod_line and zone.region='126' and zone.zone='03' and zone.flag='A' and price.flag='A');

    Thanks in Advance.

  2. #2
    Join Date
    Sep 2008
    Posts
    1
    Hi,

    If your zone table has duplicate entries of code then you will have this issue.
    Check zone table entries.

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

    you join ZONE and PRICE tables on (CODE, LINE) columns. Then you update all columns in ZONE with CODEs having in PRICE table. Extra rows are the ones with selected CODE without matching LINE column in PRICE table.
    Just issue SELECT instead of UPDATE to show them:
    Code:
    SELECT *
    FROM zone
    WHERE zone.code IN (<the first SELECT>)
    and compare it with outer join of the tables:
    Code:
    SELECT zone.*, price.line
    FROM price, zone
    WHERE zone.code=price.code(+)
      AND zone.line=price.line(+)
      AND zone.region='126'
      AND zone.area='03'
      AND zone.flag='A'
      AND price.flag(+)='A'
      AND zone.code IN (<the first SELECT>);
    [Edit: Added the last condition on the second query]
    Last edited by flyboy; 12-03-08 at 04:23.

  4. #4
    Join Date
    Dec 2008
    Posts
    3

    update with select

    Yes, update is not matching both the code & line coloumns.

    How can I do this.

    I can retrive the 60 rows.But could not update.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Yes, update is not matching both the code & line coloumns.
    Any special reason for omitting LINE column?
    How can I do this.
    What? Update the selected rows? UPDATE may be issued on query (limited to update of only one table, which is fulfilled in your case), so the easiest way would be issuing
    Code:
    UPDATE (<the first SELECT including zone.amt column>)
    SET amt = amt+10;

  6. #6
    Join Date
    Dec 2008
    Posts
    3

    Thumbs up update with select

    update zone set labor_amt = labor_amt+10 where zone.code in (select zone.code FROM price) and zone.line in ( select zone.line from price) and zone.region='126' and zone.area='03' and zone.flag='A';

    Above command is working.

    But I wanted to include one more column from price table .And it is throwing error as it cant pick up coloumn from 2nd table, throws "invalid column"

    update zone set labor_amt = labor_amt+10 where zone.code in (select zone.code FROM price) and zone.line in ( select zone.line from price) and zone.region='126' and zone.area='03' and zone.flag='A' and price.flag='A';

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Above command is working.
    What do you mean with "working"? Maybe it is "better" (choosing less rows) than the SELECT in your first post, but adding extra conditions may not filter out all data. I do not know what exactly you want (as you did not confirm the guess I stated) and why simple UPDATE of enhanced SELECT statement suggested in my previous post is not suitable.
    However the check on (CODE, LINE) existence shall (and may) be done together, e.g.
    Code:
    WHERE (code, line) IN (SELECT price.code, price.line
                           FROM price
                           WHERE price.code = zone.code
                             AND price.line = zone.line
                             AND <another conditions>)
    , or maybe better (depending on data relationship, amount and distribution) use EXISTS condition (as you filter rows which exist in PRICE table)
    Code:
    WHERE EXISTS (SELECT 1
                  FROM price
                  WHERE price.code = zone.code
                    AND price.line = zone.line
                    AND <another conditions>)

Posting Permissions

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