Subquery returned more than 1 value. This is not p

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

Other SQL Server 2008 Topics

Subquery returned more than 1 value. This is not p

Author

Topic

rh_hosseiny
Starting Member

5 Posts

Posted - 2011-05-09 : 02:06:42

Hi,
i have Problem when i want to update a foreign key..
it's my storeProcedure

ALTER PROCEDURE dbo.Doctor_Update
(
@IdDoctor int,
@Fname nvarchar(20),
@Lname nvarchar(20),
@ShNezamPezeshki varchar(10),
@IdTypeTakhasos int,
@UpdateDate nvarchar(10)
)
as
update Tbl_Doctor set
Fname=@Fname,
Lname=@Lname,
ShNezamPezeshki=@ShNezamPezeshki,
IdTypeTakhasos=(select IdTypeTakhasos=t1.IdTypeTakhasos from Tbl_TypeTakhasos as t1
inner join Tbl_Doctor as t2 on t1.IdTypeTakhasos=t2.IdTypeTakhasos),
UpdateDate=@UpdateDate

where IdDoctor=@IdDoctor
return

could anyone give me a hand?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-09 : 02:54:54

That makes no sense.
You are joining Tbl_Doctor and Tbl_TypeTakhasos on IdTypeTakhasos so you are getting the same Id and no update is needed for this column IdTypeTakhasos.

No, you're never too old to Yak'n'Roll if you're too young to die.

tross
Starting Member

20 Posts

Posted - 2011-05-09 : 12:16:31

also,
I don't see the "and t1.IdTypeTakhasos = @IdTypeTakhasos"
otherwise if both table have multiple rows, you'll get a row for every record that match in both tables.

i.e.
doc table contains 1 2 3 5 6
TypeTakhasos table contains 1 2 3 4 6
you results would be 1 2 3 6 for IDs returned. thus you'r "subquery returns ... " error

Tim,

"don't look at me, If I knew what I was talking about, I won't be working for a living"

rh_hosseiny
Starting Member

5 Posts

Posted - 2011-05-11 : 01:25:01

hi,
thank alot troos and webfred.
I added "and t1.IdTypeTakhasos = @IdTypeTakhasos" in my StoreProcedure so update work.
Also I set "Casscad" Update in Relationship but it cause null value in mother Table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources