修复表"cannot insert NULL into" SQL错误

Question

我需要在数据库中修改并保存客户订购每种产品的总次数信息。但是，当我使用INSERT INTO SELECT语句时:

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;

…我收到一个错误:

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

我要加入的表如下:

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) 
);

和

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'))
);

任何帮助将非常感激，谢谢!

Answer 1

INSERT用于创建新行，而不是用于在现有行中填充新列

使用UPDATE通过连接计算计数的子查询来更新现有行。

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