Results 1 to 6 of 6

Thread: Sub-Query Trick

  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Red face Unanswered: Sub-Query Trick

    Having 2 tables Customers and Orders And Where the Table Customers Has the columns Custnum and CustRep as keys on the orders table that in the orders table are : Cust and Rep, None of this are either Primary of Foreign Keys I'm trying to do a SUB query to Increase the CreditLimit On them + 1000 to whoever Placed an order over his credit Limit On the Ones that placed an Order Above their Credit Limit So in order to do that i thought i would go by:

    code:
    SELECT Amount
    ,CreditLimit
    FROM Orders
    ,Customers
    WHERE Amount >= CreditLimit
    AND Cust = CustNum
    AND CustRep = Rep

    This Query appears to be correct as it returns Only ONE customer who placed an order Above his creditlimit now the trick is that i need to place a single Query that update his creditLimit to 1000 so i thought using subquerys it would make sense:

    code:
    UPDATE Customers
    SET CreditLimit = creditlimit+1000
    WHERE CreditLimit = (SELECT Amount
    ,CreditLimit
    FROM Orders
    ,Customers
    WHERE Amount >= CreditLimit
    AND Cust = CustNum
    AND CustRep = Rep);

    But it doesn't work it tells me too many values any help please ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT COUNT(*)
    FROM   orders,
           customers
    WHERE  amount >= creditlimit
           AND cust = custnum
           AND custrep = rep
    post results from SQL above
    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
    Oct 2011
    Posts
    16

    Lightbulb Results as requested

    Hi
    Results:


    COUNT(*)
    ----------
    1

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE customers
    SET    creditlimit = creditlimit + 1000
    WHERE  creditlimit = (SELECT creditlimit
                          FROM   orders,
                                 customers
                          WHERE  amount >= creditlimit
                                 AND cust = custnum
                                 AND custrep = rep);
    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.

  5. #5
    Join Date
    Oct 2011
    Posts
    16

    Great :)

    Thanks a lot

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You need to learn how to do this yourself!
    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.

Posting Permissions

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