Update Error

I'm trying to updates a table in my database based using the values from another table. I get the following error when I run this update statement:

update refmd
set refmd.code = refmd_other_data.val
from refmd (nolock) join refmd_other_data (nolock) on refmd.id = refmd_other_data.refmd_id
where refmd.active = 'Y' and refmd_other_data.field_id = '114'


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

Does this mean I need to use a cursor to step through my updates?

Thanks for any help!

No, it means that there are multiple records in refmd_other_data for a specific refmd.id that matches the criteria.

You need to rethink what you are trying to acomplish.

I did end up having to use a cursor, thank you very much for your advice however...

DECLARE @md_id int

DECLARE md_cursor CURSOR
FOR
SELECT id
FROM refmd
WHERE active = 'Y'

OPEN md_cursor
FETCH NEXT FROM md_cursor into @md_id
WHILE (@@fetch_status <> -1)
begin
update refmd
set refmd.code = (SELECT val
FROM refmd_other_data
WHERE refmd_id = @md_id and field_id = 114)
WHERE refmd.id = @md_id
FETCH NEXT FROM md_cursor into @md_id
end
CLOSE md_cursor
DEALLOCATE md_cursor