CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    28

    Updating one table from another via inner join in MSSQL?

    I'm trying to update one table from another, using the following SQL:

    Code:
    UPDATE c
    SET c.state_abbr = s.state_abbr
    FROM Cities AS c
    INNER JOIN States AS s ON s.state_name = c.state_name
    When I verify the syntax, it says it's ok, but appends
    Code:
    CROSS JOIN c
    to the end of the statement. Verifying the syntax again results in an error with the syntax. Hmmm ...

    When I attempt to execute the SQL, it says
    Code:
    The following errors were encountered while parsing the contents of the SQL pane: Column or expression 'state_abbr' cannot be updated.
    Is there actually something wrong with my SQL statement? If so, what?

    Thanks.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Updating one table from another via inner join in MSSQL?

    Quote Originally Posted by SlowCoder74 View Post
    I'm trying to update one table from another, using the following SQL:

    Code:
    UPDATE c
    SET c.state_abbr = s.state_abbr
    FROM Cities AS c
    INNER JOIN States AS s ON s.state_name = c.state_name
    When I verify the syntax, it says it's ok, but appends
    Code:
    CROSS JOIN c
    to the end of the statement. Verifying the syntax again results in an error with the syntax. Hmmm ...
    I used your script in SSMS with SQL Server 2008R2 database and it works!

    Could you show your tables?
    Victor Nijegorodov

  3. #3
    Join Date
    Apr 2014
    Posts
    28

    Re: Updating one table from another via inner join in MSSQL?

    Quote Originally Posted by VictorN View Post
    I used your script in SSMS with SQL Server 2008R2 database and it works!

    Could you show your tables?
    Interesting ... I took a look at this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105943, and pasted my SQL into the New Query window. It worked!

    I was previously using the Show SQL Pane button on the toolbar, which was not working. Live and learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured