Need help in writing a Stored Procedure

[gmadhava]

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with the 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.

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors .Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
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.

Pls give me reply on how to write a stored procedure so that I can I can equate the percentage column with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

[CoolRiyaz]

Hi

Try this:

UPDATE CriticalDoctors SET CD.Percentage = TP.PercentTime
FROM CriticalDoctors CD, tblPercent TP
WHERE CD.Doctor = TP.DoctorID

[gmadhava]

I got the following error

Server: Msg 1032, Level 15, State 1, Line 1
Cannot use the column prefix 'CD'. This must match the object in the UPDATE clause 'CriticalDoctor s'.


madhav

Hi

Try this:

UPDATE CriticalDoctors SET CD.Percentage = TP.PercentTime
FROM CriticalDoctors CD, tblPercent TP
WHERE CD.Doctor = TP.DoctorID

[iburyak]

TRy this:

[PHP]
UPDATE CriticalDoctors SET Percentage = TP.PercentTime
FROM CriticalDoctors CD, tblPercent TP
WHERE CD.Doctor = TP.DoctorID[/PHP]

[CoolRiyaz]

TRy this:

[PHP]
UPDATE CriticalDoctors SET Percentage = TP.PercentTime
FROM CriticalDoctors CD, tblPercent TP
WHERE CD.Doctor = TP.DoctorID[/PHP]

Ya, this is fine