Problem with Update statement in T-SQL

Question

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!

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.

Matt Gibson · Accepted Answer · 2011-09-07 12:48:04Z

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)

edited Sep 7, 2011 at 12:48

answered Sep 7, 2011 at 12:41

Matt Gibson

38.3k10 gold badges104 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marshall Over a year ago

Yes, your solution works great.. Now i know that the problem is in trigger on TASK table and I can't drop it:/ I can't do even: UPDATE Task SET TaskStatus = 2 WHERE SysTask IN (7,8). I can do only: UPDATE Task SET TaskStatus = 2 WHERE SysTask = 8. So I have to first select SysTask ids.. and then for every single id do update.. But can I do that inside stored procedure?

sll · Accepted Answer · 2011-09-07 12:38:22Z

2

Perhaps you need to change WHERE clause like

WHERE {NOT} EXISTS (SELECT ... )

instead of

WHERE {0} = (SELECT ... )

it would be much better and faster

answered Sep 7, 2011 at 12:38

sll

62.7k22 gold badges109 silver badges157 bronze badges

Comments

user610217 · Accepted Answer · 2011-09-07 12:42:16Z

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.

answered Sep 7, 2011 at 12:42

user610217

Collectives™ on Stack Overflow

Problem with Update statement in T-SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related