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.
| Author |
Topic |
|
gmadhava
Starting Member
1 Post |
 Posted - 2007-03-29 : 07:50:32
|
| Hi Everybody,I am trying to update a column Percentage in a table named Critical Doctors with a column named PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from tblPercent.I am getting an error message for the following query.Have Two tables 1.CriticalDoctors 2.tblPercentupdate CriticalDoctors set Percentage = (select PercentTime from tblPercent)where CriticalDoctors.Doctor = (select DoctorId from tblPercent)Server: Msg 512, Level 16, State 1, Line 1Subquery 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.Pls give me reply on how to write a stored procedure so that I can equate the percentage column with one value and also check the condition with one value.Thanking you guys in advance.madhav |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-29 : 08:53:28
|
| [code]update cdset Percentage = p.PercentTimeFrom CriticalDoctors cd Join tblPercent pon cd.Doctor = p.DoctorId[/code]1. For performance reason, you should avoid using subquery as far as possible, especially correlated one (use joins over subquery)2. Use IN operator when you want to compare a column against list of values or when multiple values returned by subqueryHarsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-04-02 : 22:59:09
|
| I don't think so.... an inner join is much more effective than a WHERE IN...--Jeff Moden |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Need help in writing a Stored Procedure in MSSQL