Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Question Unanswered: Subtraction Question

    I need to deduct quantity from one table based on data from another table.

    Here's an example of my code:

    update table
    set num = (num - (select quantity from table2))
    where itemno in (select sku from table2)

    I continue to get: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated."

    This is the first time I've had a situation like this so please excuse me
    if this seems trivial.

    Thanks in Advance,
    TechRick

  2. #2
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103

    Re: Subtraction Question

    Make sure that (select quantity from table2) returns one record.
    My guess is that it is returning more than one record. It should be easy to use Query Analyser to debug this.

    Good Luck!

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Subtraction Question

    Originally posted by techrick
    I need to deduct quantity from one table based on data from another table.

    Here's an example of my code:

    update table
    set num = (num - (select quantity from table2))
    where itemno in (select sku from table2)

    I continue to get: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated."

    This is the first time I've had a situation like this so please excuse me
    if this seems trivial.

    Thanks in Advance,
    TechRick
    You have to be sure about logic in this update. According to 'where' it is possible to have more then one record in updatable table. Subquery could return more then one record too. You could use sum() in subquery if it is OK for update logic. My advice - check for logic and after that you could decide what to do with this update. It could be big underwater rock.

  4. #4
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Re: Subtraction Question

    Originally posted by snail
    You have to be sure about logic in this update. According to 'where' it is possible to have more then one record in updatable table. Subquery could return more then one record too. You could use sum() in subquery if it is OK for update logic. My advice - check for logic and after that you could decide what to do with this update. It could be big underwater rock.
    Thanks guys for the info. I ended up solving the problem by using an additional temporary table, doing the subtraction in that table and then updating the last table with the data. There is probably a more condensed way to handle this but I'm just glad to have this worked out. Please let me know if you see a way to improve on that which is below. Thanks.

    Here's what I ended up doing:

    SELECT SKU, TOTAL, QTY_SOLD, NEW_TOTAL
    INTO TABLE3
    FROM ITEMS, TABLE2
    WHERE ITEMNO = SKU


    UPDATE TABLE3
    SET NEW_TOTAL = TOTAL - QTY_SOLD


    UPDATE ITEMS
    SET TOTAL = NEW_TOTAL FROM TABLE3
    WHERE ITEMNO = SKU

Posting Permissions

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