Update price of products that aren't selling : Multiple Row Subquery « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. Multiple Row Subquery
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6  );

Table created.

SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date    DATE,
  5       quantity      NUMBER(4,2)
  6  );

Table created.

SQL>
SQL>
SQL> INSERT INTO product_order VALUES ('Product 1', 'CA', '14-JUL-03', 1);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 2', 'BB', '14-JUL-03', 75);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 3', 'GA', '14-JUL-03', 2);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 4', 'GA', '15-JUL-03', 8);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 5', 'LB', '15-JUL-03', 20);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 6', 'CA', '16-JUL-03', 5);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 7', 'CA', '17-JUL-03', 1);

1 row created.

SQL>
SQL>
SQL> INSERT INTO product VALUES ('Product 1', 99,  1,    '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Product 2', 75,  1000, '15-JAN-02');

1 row created.

SQL> INSERT INTO product VALUES ('Product 3', 50,  100,  '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Product 4', 25,  10000, null);

1 row created.

SQL> INSERT INTO product VALUES ('Product 5', 9.95,1234, '15-JAN-04');

1 row created.

SQL> INSERT INTO product VALUES ('Product 6', 45,  1,    TO_DATE('December 31, 2008, 11:30 P.M.','Month dd, YYYY, HH:MI P.M.'));

1 row created.

SQL>
SQL> SELECT * FROM  product;

PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Product 1                            99                1 15-JAN-03
Product 2                            75             1000 15-JAN-02
Product 3                            50              100 15-JAN-03
Product 4                            25            10000
Product 5                          9.95             1234 15-JAN-04
Product 6                            45                1 31-DEC-08

6 rows selected.

SQL>
SQL> UPDATE product
  2  SET    product_price = product_price * .9
  3  WHERE  product_name NOT IN (SELECT DISTINCT product_name FROM product_order);

0 rows updated.

SQL>
SQL> SELECT * FROM  product;

PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Product 1                            99                1 15-JAN-03
Product 2                            75             1000 15-JAN-02
Product 3                            50              100 15-JAN-03
Product 4                            25            10000
Product 5                          9.95             1234 15-JAN-04
Product 6                            45                1 31-DEC-08

6 rows selected.

SQL>
SQL> drop table product;

Table dropped.

SQL> drop table product_order;

Table dropped.








2.38.Multiple Row Subquery
2.38.1.Writing Multiple Row Subqueries
2.38.2.Using IN with a Multiple Row Subquery
2.38.3.Update price of products that aren't selling
2.38.4.Multi-row subqueries: Show products that aren't selling
2.38.5.Uses NOT IN to check if an id is not in the list of id values in the employee table
2.38.6.Using ANY with a Multiple Row Subquery
2.38.7.Using ALL with a Multiple Row Subquery
2.38.8.Writing Multiple Column Subqueries
2.38.9.Select from another select statement
2.38.10.Subqueries That Return Multiple Results
2.38.11.First three rows from subquery