SQL Server 为什么我不能在SQL中用SELECT插入？

ovfsdjhp 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(141)

我试图更新我的表程序集。有人能理解为什么它不工作吗？为什么我不能用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

sql-server

来源：https://stackoverflow.com/questions/74708317/why-i-can-not-insert-with-select-in-sql

1条答案

按热度按时间

ggazkfy81#

该表中已经有行了，所以应该使用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

或者简单地使校验和成为计算列：

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

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 为什么我不能在SQL中用SELECT插入？

1条答案

相关问题

热门标签

最新问答