Help with Update - 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 2000 Forums

Transact-SQL (2000)

Help with Update

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-08-02 : 08:04:03

I would like to update a table based on the below query, but i keep getting the
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.
"

update tbl_Voy_RCATransfer
set EmployeeID_HR = (SELECT dbo.tbl_login_ids.empl_id
FROM dbo.tbl_Voy_RCATransfer INNER JOIN
dbo.tbl_login_ids ON dbo.tbl_Voy_RCATransfer.Source_System = dbo.tbl_login_ids.source_system AND
dbo.tbl_Voy_RCATransfer.EmployeeID COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.tbl_login_ids.user_id
WHERE (dbo.tbl_login_ids.source_system = 'voy'))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 08:23:24

your subquery is returning more than one value.

use inner join instead


update tbl_Voy_RCATransfer
set   EmployeeID_HR = dbo.tbl_login_ids.empl_id
from  dbo.tbl_Voy_RCATransfer
inner join dbo.tbl_login_ids 
ON    dbo.tbl_Voy_RCATransfer.Source_System = dbo.tbl_login_ids.source_system 
AND   dbo.tbl_Voy_RCATransfer.EmployeeID COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.tbl_login_ids.user_id
WHERE (dbo.tbl_login_ids.source_system = 'voy')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources