Post reply

simple update doesn''t work

  • January 16, 2006 at 9:53 am

    #96443

    (If this is the wrong place to post this, please redirect me.)

    I am trying to do what I think is a simple UPDATE query in a query window in the SSE2005 Management Studio. I have a table named "ss_comments" and a view named "ac_course_comments". This query:

    update ss_comments s, ac_course_comments a

    set s.comments = a.comments

    where s.course_code = a.course_id

    ...is attempting to update the table with data from the view when the course_code and course_id fields are equal.

    But I'm getting this error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 's'.

    I don't get it. Does anyone else?

    TIA

    --doug

  • January 16, 2006 at 9:59 am

    #614943

    How about:

    update ss_comments set
comments = a.comments
from ss_comments s inner join ac_course_comments a
on s.course_code = a.course_id

    You can only update one table in an Update statement.  Use the FROM clause to do your join's and where's.

    Hope this helps



    Mark

  • January 16, 2006 at 10:07 am

    #614946

    Thanks, Mark.

    I used your code and got this error:

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'ss_comments'.

    There is definitely a table named 'ss_comments' in this database. So why the error?

    --doug

  • January 16, 2006 at 10:17 am

    #614949

    Sorry. It turned out the wrong database was being used for the query. Everything worked as hoped when I switched to the correct one.

    Thanks so much for your help, Mark!

    --doug

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply