0

I need to modify and save in a database information about the total number of times each product has been ordered by the customers. However when I use this INSERT INTO SELECT statement:

ALTER TABLE PRODUCT
ADD ORDER_QUANTITY NUMBER(2);

INSERT INTO PRODUCT(ORDER_QUANTITY)
SELECT COUNT(ORDER_DETAIL.PRODUCT_NAME)
FROM PRODUCT
LEFT JOIN ORDER_DETAIL ON ORDER_DETAIL.PRODUCT_NAME = PRODUCT.PRODUCT_NAME
GROUP BY PRODUCT.PRODUCT_NAME;

...I receive an error:

cannot insert NULL into ("USER"."PRODUCT"."PRODUCT_NAME")

The tables I am joining are below:

CREATE TABLE ORDER_DETAIL
(
ORDER_ID         NUMBER(9)  NOT NULL,
PRODUCT_NAME         VARCHAR(40)    NOT NULL,
UNIT_PRICE       NUMBER(10,2)   DEFAULT 0.0,
QUANTITY         NUMBER(9)  DEFAULT 1,
DISCOUNT         NUMBER(4,2)    DEFAULT 0.0,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),
CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME) REFERENCES PRODUCT (PRODUCT_NAME),
CONSTRAINT CK_ORDER_DETAIL_UNIT_PRICE CHECK (UNIT_PRICE >= 0), 
CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),
CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1) 
);

AND

CREATE TABLE PRODUCT
(
PRODUCT_NAME    VARCHAR(40)     NOT NULL,
SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
CATEGORY_NAME   VARCHAR(30) NOT NULL,
QUANTITY_PER_UNIT   VARCHAR(20)         NULL,
UNIT_PRICE      NUMBER(10,2)    DEFAULT 0,
UNITS_IN_STOCK  NUMBER(9)   DEFAULT 0,
UNITS_ON_ORDER  NUMBER(9)   DEFAULT 0,
REORDER_LEVEL   NUMBER(9)   DEFAULT 0,
DISCONTINUED    CHAR(1)     DEFAULT 'N',
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

Any help would be much appreciated,thanks!

Barmar
  • 741,623
  • 53
  • 500
  • 612
bean
  • 329
  • 1
  • 5
  • 13
  • 1
    Are you trying to `INSERT` a new record, or `UPDATE` an existing record? The code is attempting the former, and the error tells you exactly what's wrong. You're only providing a value for the `ORDER_QUANTITY` column and not for any of the other columns, most of which specify `NOT NULL`. It's not really clear what you're expecting this `INSERT` statement to do or why. – David Aug 06 '21 at 19:15
  • @David apologies. I just updated the code with ``ALTER TABLE`` statement. Are you saying I need to provide values for all the columns which specify NOT NULL? – bean Aug 06 '21 at 19:17
  • 1
    If you're inserting a new record, yes. `NOT NULL` means the column doesn't allow `NULL` values in any of the table records. – David Aug 06 '21 at 19:18
  • The ORDER_QUANTITY field does not exist in the product table? !!!!!!!!! – Meysam Asadi Aug 06 '21 at 19:19
  • 1
    @MeysamAsadi He added it with the `ALTER TABLE` query at the top. – Barmar Aug 06 '21 at 19:19
  • 1
    You should be using `UPDATE` to fill in the new column in existing rows. `INSERT` is for creating new rows. – Barmar Aug 06 '21 at 19:20
  • @Barmar I am creating new rows in the ``ORDER_QUANTITY`` column – bean Aug 06 '21 at 19:22
  • 1
    You don't create rows in a single column. A row contains all the columns about a specific product. – Barmar Aug 06 '21 at 19:22
  • 1
    @bean: That's just not how a table works. You don't create rows *per column*. It sounds like this column shouldn't belong to this table. Why is it needed? Can't the quantity of orders for any given product be calculated by the records of orders? – David Aug 06 '21 at 19:23
  • `ALTER TABLE` creates the `ORDER_QUANTITY` columns in all the rows. You're just trying to fill it in. – Barmar Aug 06 '21 at 19:33

1 Answers1

3

INSERT is for creating new rows, not for filling in the new column in existing rows

Use UPDATE to update the existing rows by joining with a subquery that calculates the counts.

UPDATE p
SET p.order_quantity = COALESCE(d.quantity, 0)
FROM product p
LEFT JOIN (
    SELECT product_name, COUNT(*) AS quantity
    FROM order_detail
    GROUP BY product_name
) d ON d.product_name = p.product_name
Barmar
  • 741,623
  • 53
  • 500
  • 612