Fix "cannot insert NULL into" SQL Error on table

Question

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!

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
– David, Commented Aug 6, 2021 at 19:15
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
– David, Commented Aug 6, 2021 at 19:18
@MeysamAsadi He added it with the ALTER TABLE query at the top. — Barmar
– Barmar, Commented Aug 6, 2021 at 19:19
You should be using UPDATE to fill in the new column in existing rows. INSERT is for creating new rows. — Barmar
– Barmar, Commented Aug 6, 2021 at 19:20
You don't create rows in a single column. A row contains all the columns about a specific product. — Barmar
– Barmar, Commented Aug 6, 2021 at 19:22

Barmar · Accepted Answer · 2021-08-06 19:38:09Z

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

edited Aug 6, 2021 at 19:38

answered Aug 6, 2021 at 19:26

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

bean Over a year ago

Missing SET keyword

Barmar Over a year ago

SET p.order_quantity = IFNULL(d.quantity, 0)

bean Over a year ago

In the first line UPDATE product AS p, there is an error line under p

Barmar Over a year ago

Are you sure you're using MySQL, not SQL-Server?

bean Over a year ago

I am using SQL-Server

|

Collectives™ on Stack Overflow

Fix "cannot insert NULL into" SQL Error on table

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related