Subquery returned more than 1 value....

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)

Subquery returned more than 1 value....

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-15 : 11:25:22

Hello,

What would cause the above error message ?

My code is :


update dbo.tbl_EmployeeDetails
set  EEMemo = 
( SELECT     CONVERT(varchar(8000), dbo.vw_MainUpdateData.EEMemo) AS EEMemo
FROM         dbo.vw_MainUpdateData INNER JOIN
             dbo.tbl_EmployeeDetails ON dbo.vw_MainUpdateData.Employee_Number = dbo.tbl_EmployeeDetails.EmployeeNumber)

Thanks in advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-15 : 11:26:18

It simply means that your inner SELECT query is returning more than 1 record which is not allowed.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-15 : 11:28:28

I thought that, because when I take the following portion of the code into a view, it displays the EEMemo field for every person in the list.


SELECT     CONVERT(varchar(8000), dbo.vw_MainUpdateData.EEMemo) AS EEMemo
FROM         dbo.vw_MainUpdateData INNER JOIN
             dbo.tbl_EmployeeDetails ON dbo.vw_MainUpdateData.AutoRef = dbo.tbl_EmployeeDetails.TechnicianID

I'm not quite sure how I get round this though - all I want to do is update the EEMemo field for every person.
Can I do this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 11:29:20

Then you have at least two employees with same employeenumber.

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-15 : 11:34:50

Well I've put the following into a view, and it showed no records :


SELECT     EmployeeNumber, COUNT(*) AS Expr1
FROM         dbo.tbl_EmployeeDetails
GROUP BY EmployeeNumber
HAVING      (COUNT(*) > 1)

I also put the following into a view to see if it was my view causing a problem, and this also came back with no records.


SELECT     Employee_Number, COUNT(*) AS Expr1
FROM         dbo.vw_MainUpdateData
GROUP BY Employee_Number
HAVING      (COUNT(*) > 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 11:38:57

If you look very closely, you can see that you JOIN the view with the table. Is that necessary?

update	dbo.tbl_EmployeeDetails
set	EEMemo =	(
				SELECT		CONVERT(varchar(8000), dbo.vw_MainUpdateData.EEMemo) AS EEMemo
				FROM		dbo.vw_MainUpdateData
				INNER JOIN	dbo.tbl_EmployeeDetails ON dbo.vw_MainUpdateData.Employee_Number = dbo.tbl_EmployeeDetails.EmployeeNumber
			)

Try this

UPDATE		x
SET		x.EEMemo = CONVERT(VARCHAR(8000), y.EEMemo)
FROM		dbo.tbl_EmployeeDetails AS x
INNER JOIN	dbo.vw_MainUpdateData AS y ON y.Employee_Number = x.EmployeeNumber

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-18 : 10:54:38

Thanks Peso - you were right, the join wasn't necessary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources