sql-server - SQL 函数 - 当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时,这是不允许的
问题描述
CREATE TABLE Property542
(
PID INT IDENTITY(1,1) NOT NULL ,
PropertyID AS RIGHT ('PID0000' + CAST(PID AS VARCHAR(10)), 10) PERSISTED
CONSTRAINT PK_Property PRIMARY KEY CLUSTERED,
Availability BIT NOT NULL,
P_Address VARCHAR(30) NOT NULL,
MonthlyRent INT NOT NULL,
Max_tenants INT NOT NULL,
Type_of_Property VARCHAR(15) NOT NULL,
--NIC varchar(10) FOREIGN KEY REFERENCES Landlord (NIC)
)
CREATE TABLE DetachedHouse768
(
PropertyID VARCHAR(10) PRIMARY KEY,
Dept VARCHAR(30) NOT NULL,
)
DECLARE @id TABLE (ID varchar(10))
INSERT INTO Property542
OUTPUT INSERTED.PropertyID INTO @id(ID)
VALUES (1, 'Kandy Road,Pasyala', 5000, 4, 'DetachedHouse'),
(0, 'Kale para,Mathara', 7500, 2, 'Flat'),
(1, 'No34,New Town,Kaluthara', 10000, 1, 'TerransedHouse');
INSERT INTO DetachedHouse768(PropertyID, Dept)
VALUES ((SELECT ID FROM @id), 'room'),
((SELECT ID FROM @id), 'floor'),
((SELECT ID FROM @id), 'House');
我收到此错误:
子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时,这是不允许的。该语句已终止。
我想使用这种方法一次性插入更多数据。
解决方案
假设您打算DetachedHouse768
为之前插入的每个 Property542 行插入 3 行(房间、楼层、房屋),则 DetachedHouse768 上的主要行不正确。PropertyID
单独不是唯一的,因此它可能是两者的复合键PropertyID
并且Dept
是唯一的。
下面是一个 INSERT...SELECT 示例CROSS JOIN
:
CREATE TABLE Property542
(
PID int IDENTITY(1,1) NOT NULL ,
PropertyID AS RIGHT ('PID0000' + CAST(PID AS VARCHAR(10)), 10) PERSISTED
CONSTRAINT PK_Property PRIMARY KEY CLUSTERED,
Availability bit NOT null,
P_Address varchar(30) Not null,
MonthlyRent int Not null,
Max_tenants int Not null,
Type_of_Property varchar(15) Not null
);
CREATE TABLE DetachedHouse768
(
PropertyID varchar(10),
Dept varchar(30) Not null,
CONSTRAINT PK_DetachedHouse768 PRIMARY KEY CLUSTERED(PropertyID,Dept)
);
DECLARE @id TABLE (ID varchar(10))
INSERT INTO Property542
OUTPUT INSERTED.PropertyID INTO @id(ID)
VALUES (1,'Kandy Road,Pasyala',5000,4,'DetachedHouse')
,(0,' Kale para,Mathara',7500,2,'Flat')
,(1,'No34,New Town,Kaluthara',10000,1,'TerransedHouse');
INSERT INTO DetachedHouse768( PropertyID ,Dept)
SELECT ID, Dept
FROM @id
CROSS JOIN (VALUES
('room')
,('floor')
,('house')
) AS Depts(Dept);
推荐阅读
- python - 如何使用 PySide2 实现响应式画廊视图
- matlab - Warning: Unable to load Toolbox Path Cache in a for loop
- c - 有人可以解释模数是如何工作的吗?
- sql-server - 在 SQL Server 中计算每周给定开始日期的行数
- angular - 如何在 Angular 和 Firebase 中进行三向数据绑定?
- android - Cordova ble.write i can't send data (Android)
- java - 尝试在 java 中使用递归创建指数计算器时出现堆栈溢出错误
- angularjs - 错误:[$injector:unpr] 未知提供程序 - 将 AngularJS1 迁移到 Angular6
- android - How to custom claims token to firebase firestore rules via Android App?
- debugging - Having an issue importing a tlb file into my MFC Application