CODEWITHSUNDEEP
sqloracle-database
mako
mako

Reputation: 664

Update a table by referencing a value from a subquery in the where clause

I have the following query where I attempt to update the customers table and increase a customers credit limit when they have an order that exceeds their credit limit, and I increase their credit limit to their maximum order + $1,000

UPDATE CUSTOMERS SET CREDIT_LIMIT = Max_Order + 1,000 
WHERE (SELECT max(AMOUNT) as Max_Order, CUST_NUM
FROM ORDERS, CUSTOMERS WHERE CUST_NUM=CUST AND AMOUNT > CREDIT_LIMIT GROUP BY CUST_NUM)

The problem I am running into is that I cannot use my Max_Order col which is referenced in my subquery to set the customers credit limit. Is there another way I can go about setting the credit limit to the maximum value of their order when they have exceeded their credit limit?

Here is the sql fiddle: http://sqlfiddle.com/#!4/63dc7/69

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

One approach is to use merge. Another is to use two subqueries, one in the where and one for the set:

UPDATE CUSTOMERS
    SET CREDIT_LIMIT = (SELECT MAX(AMOUNT) + 1000 
                        FROM ORDERS o 
                        WHERE CUST_NUM = CUST AND AMOUNT > CREDIT_LIMIT
                       )
    WHERE EXISTS (SELECT 1
                  FROM ORDERS o
                  WHERE CUST_NUM = CUST AND AMOUNT > CREDIT_LIMIT
                 );

Upvotes: 1

Related Questions