Update/Select query help please... 1

[Webkins]

Here is the MSSQL 2000 code I am trying to use:

update mwo.dbo.mwo_backup set completed_week = (select DatePart(week, responsible_date) from mwo.dbo.mwo_backup where responsible_status = 'Complete')

I get the following error:

Server: Msg 512, Level 16, State 1, Line 1
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.

Any ideas? Thanks a bunch!

 

[gmmastros]

The problem is that you are using a subquery (the part in the parenthesis), but that subquery is returning multiple rows.

If you run just the sub-query separately, how many rows do you get?

select DatePart(week, responsible_date) from mwo.dbo.mwo_backup where responsible_status = 'Complete'

Of these multiple rows, which one do you want to use?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[softhemc]

I haven't got SQL Server on this machine, but from memory I think something like this would do what you need

update mwo.dbo.mwo_backup
  set completed_week = DatePart(week, responsible_date)
where responsible_status = 'Complete'