Thread: Update Error

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    6
    Rep Power
    0

    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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    6
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo