Home » SQL & PL/SQL » SQL & PL/SQL » SQL Update Statement help
SQL Update Statement help [message #199705] Wed, 25 October 2006 22:15 Go to next message
srajan72
Messages: 20
Registered: February 2006
Junior Member
This is not a real-life example. I have tried to simplify it for clarity:

I have the following Customer table:

CREATE TABLE CUSTOMERS
        (CUST_ID NUMBER,
         NUM_OF_ITEMS_GT_5 NUMBER);


And an orders table:
CREATE TABLE ORDERS
       (CUST_ID NUMBER,
        ITEM VARCHAR2(25),
        QUANTITY NUMBER);


The customer table has following records:
INSERT INTO CUSTOMERS VALUES (1,NULL);
INSERT INTO CUSTOMERS VALUES (2,NULL);

And orders table has the following records:
INSERT INTO ORDERS VALUES (1,'Books',2);
INSERT INTO ORDERS VALUES (1,'Books',4);
INSERT INTO ORDERS VALUES (1,'Pencils',12);
INSERT INTO ORDERS VALUES (1,'Pen',3);

INSERT INTO ORDERS VALUES (2,'Pen',1);
INSERT INTO ORDERS VALUES (2,'Pen',6);
INSERT INTO ORDERS VALUES (2,'Books',4);

** I need to count number of different items with a total order quantity more than 5 and update the value in the Customers table. **

In the example above, Customer '1' ordered two items with a total quantity > 5 (Books and Pencil). Customer '2' ordered just one item > 5 (Pen).

How can I achieve this through an update statement. Oracle doesnt like the reference to C.CUST_ID in the in-line table.

UPDATE CUSTOMERS C
   SET NUM_OF_ITEMS_GT_5 = (SELECT COUNT(*)
                              FROM (SELECT 0.CUST_ID,
                                           O.ITEM,
                                           SUM(O.QUANTITY)
                                      FROM ORDERS O
                                     WHERE O.CUST_ID   =  C.CUST_ID -- Oracle doesnt like this reference
                                     GROUP BY O.CUST_ID, O.ITEM
                                     HAVING SUM(O.QUANTITY) > 5)
                            );

I'd really appreciate your help with this.
Re: SQL Update Statement help [message #199721 is a reply to message #199705] Thu, 26 October 2006 00:36 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Hi,

move the where clause one level up.

UPDATE CUSTOMERS C
   SET NUM_OF_ITEMS_GT_5 = (SELECT COUNT(*)
                              FROM (SELECT 0.CUST_ID,
                                           O.ITEM,
                                           SUM(O.QUANTITY)
                                      FROM ORDERS O
                                     GROUP BY O.CUST_ID, O.ITEM
                                     HAVING SUM(O.QUANTITY) > 5)
                            WHERE CUST_ID=C.CUST_ID );
Re: SQL Update Statement help [message #199726 is a reply to message #199705] Thu, 26 October 2006 00:54 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


UPDATE CUSTOMERS SET NUM_OF_ITEMS_GT_5=
    (SELECT X.TOT_QTY FROM
                          (SELECT CUST_ID,COUNT(*)TOT_QTY FROM
                                                   (SELECT CUST_ID,ITEM FROM ORDERS GROUP BY                                          
                                                    CUST_ID,ITEM HAVING SUM(QUANTITY)>5 
                                                    )
                          GROUP BY CUST_ID)X WHERE X.CUST_ID=CUSTOMERS.CUST_ID)



regards,
Previous Topic: to get highest sal dept-wise
Next Topic: About Oracle 9i Forms & Reports CD
Goto Forum:
  


Current Time: Wed Dec 07 18:45:54 CST 2016

Total time taken to generate the page: 0.08051 seconds