Need help with updating rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with updating rows

Author

Topic

cherry lyn
Starting Member

2 Posts

Posted - 2011-07-12 : 03:37:14

Hi,
Pls can someone help me. I have two tables, dept and deptteam, each already with existing records. I'd like to add a new column to dept called hasTeams and should be populated with either 'Y' or 'N'. My problem is how to update each row in dept with these values. My code:


CREATE FUNCTION deptwithnoteams()
Returns table
Return Select * from dept where deptID not in (Select deptID from deptteam)

CREATE PROCEDURE updatehasTeams()
As
Begin
Update dept set hasTeams='Y' where deptID not in (Select * from dbo.deptwithnoteams())
Update dept set hasTeams='N' where hasTeams is Null
End

My proc is throwing this error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Pls is there any other way to do this? Thanks much in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-12 : 04:07:17

[code]
Update d
set hasTeams = case when not exists (select * from deptteam x where x.deptID = d.deptID)
then 'Y'
else 'N'
end
from dept d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cherry lyn
Starting Member

2 Posts

Posted - 2011-07-12 : 06:02:11

Thank you so much khtan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources