Microsoft SQL Server - 限制子查询 [英] Microsoft SQL Server - restricting subqueries

问题描述

这主要是一个好奇的问题.我刚刚遇到过在测试数据库上有以下查询的情况:

更新表设置 column1 = 1where column2 in (1,2)

但这一直在执行，并出现子查询返回多个值的错误.

现在我检查以确保我没有多个身份密钥或in"值是唯一的.因此，出于所有意图和目的，这不应该发生.

检查数据库的实时副本，相同的查询没有问题.因此，最后，我的问题是:

您可以对会创建此类场景的 Microsoft SQL Server 设置或数据库结构做些什么?

解决方案

您可以对会创建此类场景的 Microsoft SQL Server 设置或数据库结构做些什么?

正如评论中提到的，您可能编写的触发器很差.示例场景:

CREATE TABLE aud(column2 INT, comment NVARCHAR(150));创建表选项卡(第 1 列 INT，第 2 列 INT)；插入 aud(column2) 值 (1),(2),(3);INSERT INTO tab(column1, column2) VALUES (0,1),(-1, 2), (-2,3);走创建触发器 trg_tab_i ON 选项卡更新作为开始更新审计SET 注释 = '更改值...'WHERE column2 = (SELECT column2 FROM insert);结尾走更新选项卡设置列 1 = 1WHERE column2 in (1,2);

<块引用>

Msg 512, Level 16, State 1, Procedure trg_tab_i, Line 5 [Batch Start Line 19]

子查询返回了 1 个以上的值.当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.

UPDATE 选项卡设置列 1 = 1WHERE column2 in (1);--(1 行受影响)--(1 行受影响)

DBFiddle 演示

当只有一行受到影响时，一切正常.

This is mostly a curiosity question. I've just experienced a situation where on a test database I had the following query:

update table 
set column1 = 1 
where column2 in (1,2)

But this kept executing with the error that subquery returned more than one value.

Now I checked to make sure I did not have multiple identity keys or that the 'in' values were unique. so for all intents and purposes this should not have happened.

Checking on the LIVE copy of the database, same query did not have an issue. Hence, finally, my question is:

What can you do to the Microsoft SQL Server settings or database structure that would create such a scenario?

解决方案

What can you do to the Microsoft SQL Server settings or database structure that would create such a scenario?

As mentioned in comments you probably have poorly written trigger. Sample scenario:

CREATE TABLE aud(column2 INT, comment NVARCHAR(150));
CREATE TABLE tab(column1 INT, column2 INT);

INSERT INTO aud(column2) VALUES (1),(2),(3);
INSERT INTO tab(column1, column2) VALUES (0,1),(-1, 2), (-2,3);
GO

CREATE TRIGGER trg_tab_i ON tab 
FOR UPDATE
AS
BEGIN
   UPDATE aud
   SET comment = 'Changed value ...'
   WHERE column2 = (SELECT column2 FROM inserted);
END
GO


UPDATE tab
SET column1 = 1 
WHERE column2 in (1,2);

Msg 512, Level 16, State 1, Procedure trg_tab_i, Line 5 [Batch Start Line 19]

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

UPDATE tab
SET column1 = 1 
WHERE column2 in (1);
-- (1 row(s) affected)
-- (1 row(s) affected)

DBFiddle Demo

When only one row is affected everything works.

这篇关于Microsoft SQL Server - 限制子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！