I have two tables:
我有两个表:
CREATE TABLE [dbo].[Task](
[SysTask] [int] IDENTITY(1,1) NOT NULL,
[TaskStatus] [int] NOT NULL,
)
CREATE TABLE [dbo].[Queue](
[SysQueue] [int] IDENTITY(1,1) NOT NULL,
[SysTask] [int] NOT NULL,
[QueueStatus] [int] NOT NULL,
)
One TASK has many Queue records. I know these names are confusing a little bit. I need to update all the records from the table TASK, which have no record in the table QUEUE with QueueStatus < 4. And this query drives me crazy!
一个任务有许多队列记录。我知道这些名字有点混乱。我需要更新表任务中的所有记录,这些记录在QueueStatus < 4的表队列中没有记录。这个问题让我抓狂!
Here is what I've tried:
以下是我尝试过的:
UPDATE Task SET SysTaskStatus = 3
WHERE SysTaskStatus <> 3 AND [SysTask] NOT IN ((SELECT tq.SysTask FROM [dbo].[TaskQueue] tq WHERE [SysTask] = tq.SysTask AND tq.[SysTaskQueueStatus] IN (1,2,3)))
UPDATE Task SET SysTaskStatus = 3
WHERE EXISTS (SELECT 1 FROM [dbo].[TaskQueue] WHERE Task.[SysTask] = [SysTask] AND [SysTaskQueueStatus] <4 HAVING COUNT(*)=0)
UPDATE Task SET SysTaskStatus = 3
WHERE 0 = (SELECT SysTask FROM [dbo].[TaskQueue] WHERE Task.[SysTask] = SysTask AND [SysTaskQueueStatus] < 4)
For all this queries there is always the same error:
对于所有这些查询,总是有相同的错误:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
子查询返回的值大于1。当子查询后跟=、!=、<、<=、>、>=或子查询用作表达式时,不允许这样做。声明已被终止。
3 个解决方案
#1
2
If I've understood you correctly, this should do the trick:
如果我没理解错的话,这个方法应该能奏效:
UPDATE
Task
SET
SysTaskStatus = 3
WHERE
NOT EXISTS (SELECT * FROM TaskQueue WHERE QueueStatus < 4 AND Task.SysTask = TaskQueue.SysTask)
#2
2
Perhaps you need to change WHERE clause like
也许您需要更改WHERE子句like
WHERE {NOT} EXISTS (SELECT ... )
instead of
而不是
WHERE {0} = (SELECT ... )
it would be much better and faster
会越来越好,越来越快
#3
0
WHERE 0 = (SELECT SysTask FROM [dbo].[TaskQueue] WHERE Task.[SysTask] = SysTask AND [SysTaskQueueStatus] < 4)
this clause is expecting a scalar value, and you are providing a table value.
这个子句期望的是一个标量值,而您提供的是一个表值。
#1
2
If I've understood you correctly, this should do the trick:
如果我没理解错的话,这个方法应该能奏效:
UPDATE
Task
SET
SysTaskStatus = 3
WHERE
NOT EXISTS (SELECT * FROM TaskQueue WHERE QueueStatus < 4 AND Task.SysTask = TaskQueue.SysTask)
#2
2
Perhaps you need to change WHERE clause like
也许您需要更改WHERE子句like
WHERE {NOT} EXISTS (SELECT ... )
instead of
而不是
WHERE {0} = (SELECT ... )
it would be much better and faster
会越来越好,越来越快
#3
0
WHERE 0 = (SELECT SysTask FROM [dbo].[TaskQueue] WHERE Task.[SysTask] = SysTask AND [SysTaskQueueStatus] < 4)
this clause is expecting a scalar value, and you are providing a table value.
这个子句期望的是一个标量值,而您提供的是一个表值。