为什么我不能在SQL中插入SELECT ?

Question

我正在尝试更新我的表程序集。有人能理解为什么它不起作用吗?为什么我不能插入到一个现有的表与SELECT语句?

DROP TABLE IF EXISTS Assembly;
GO
CREATE TABLE Assembly
(
AssemblyID  INTEGER,
Part        VARCHAR(100),
checksum    INT, 
PRIMARY KEY (AssemblyID, Part)
);
GO
INSERT INTO Assembly (AssemblyID, Part) 
VALUES (1001, 'Bolt'), (1001, 'Screw'),
(2002, 'Nut'), (2002, 'Washer'),
(3003, 'Toggle'), (3003, 'Bolt');
GO
INSERT INTO Assembly (checksum)
VALUES (SELECT checksum(AssemblyID, Part) AS checksum
FROM Assembly)
SELECT * 
FROM Assembly

Answer 1

表中已经有行了。所以你应该UPDATE而不是INSERT。

如

DROP TABLE IF EXISTS Assembly;
GO
CREATE TABLE Assembly
(
AssemblyID  INTEGER,
Part        VARCHAR(100),
checksum int, 
PRIMARY KEY (AssemblyID, Part)
);
GO
INSERT INTO Assembly (AssemblyID, Part) VALUES  
(1001,'Bolt'),(1001,'Screw'),(2002,'Nut'),
(2002,'Washer'),(3003,'Toggle'),(3003,'Bolt');
GO
update Assembly set checksum = checksum(AssemblyID, Part) 
SELECT * 
FROM Assembly

或者直接将checksum设为计算列:

CREATE TABLE Assembly
(
AssemblyID  INTEGER,
Part        VARCHAR(100),
checksum as checksum(AssemblyID, Part) persisted, 
PRIMARY KEY (AssemblyID, Part)
);