Question

我有两个表格:

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!

我在此尝试:

    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)

对于所有这些询问,总是有同样的错误:

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.

Answer 1

如果我正确理解你的话,这就应当做到:

UPDATE
  Task
SET 
  SysTaskStatus = 3
WHERE
  NOT EXISTS (SELECT * FROM TaskQueue WHERE QueueStatus < 4 AND Task.SysTask = TaskQueue.SysTask)

Answer 2

也许你需要修改《家庭环境公约》条款,如:

WHERE {NOT} EXISTS (SELECT ... )

而不是

WHERE {0} = (SELECT ... )

更好和更快

Answer 3

WHERE 0 = (SELECT SysTask FROM [dbo].[TaskQueue] WHERE Task.[SysTask] = SysTask AND [SysTaskQueueStatus] < 4)

该条款预计会具有一定价值,你正在提供一张表值。

友情链接