Question about query error received

  • I have a question about some code that I wrote. I don’t understand why I got an error message and figure I’m missing something (obviously), but have no idea where to start to find out what I’m missing.

    I was doing an update from one table (main2) to another table (main). I had taken a portion of the data from the table main and done a SELECT * INTO main2. What I needed to do was assign an orderid to the records in the table and I wanted to do that in a separate table so that if I goofed, it was easy to repair.

    Everything was fine until I started to update the main table.

    I wrote this SELECT statement to make sure I was matching things up before I executed.

    select m.listid, m2, listid, m.orderid, m2.orderid

    from dbo.main m

    inner join dbo.main m2

    on m.listid = m2.listid

    This showed me that I was matching the main.listid to main2.listid and that the main.orderid was null (that’s the field I’m updating) and that main2.orderid had been assigned or was null.

    So, the next thing I do is my update statement, just replacing the SELECT with an UPDATE <table> SET <field1> = <field2>.

    update dbo.main

    set m.orderid = m2.orderid

    from dbo.main m

    inner join dbo.main2 m2

    on m.listid = m2.listid

    and I get this error message:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "u.orderid" could not be bound.

    When I change the first instance of m.orderid to main.orderid, the update works just fine.

    update dbo.main --This update works

    set main.orderid = m2.orderid

    from dbo.main m

    inner join dbo.main2 m2

    on m.listid = m2.listid

    Why did the first one not work? I’m assuming it has something to do with the alias assigned and when SQL gets to figuring out the alias, but can someone point me to a reference that says why I’m getting this behavior?

    -- Kit

  • You may be able to alias the table name in the update statement in Oracle but

    The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.

  • update m

    set m.orderid = m2.orderid

    from dbo.main m

    inner join dbo.main2 m2

    on m.listid = m2.listid

  • jcdyntek (1/13/2010)


    You may be able to alias the table name in the update statement in Oracle but

    The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.

    Try this on SQL Server 2005

    Use AdventureWorks

    UPDATE a

    SET [City] = 'TestIt'

    FROM Person.Address a

    where AddressID = 1

    --Put it back to what it was

    UPDATE a

    SET [City] = 'Bothell'

    FROM Person.Address a

    where AddressID = 1

  • Hello

    The UPDATE and the SET apply to the same table.

    Ideally the first table listed after FROM is also the target of the update.

    In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris-439714 (1/15/2010)


    Hello

    The UPDATE and the SET apply to the same table.

    Ideally the first table listed after FROM is also the target of the update.

    In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET

    Ah. Thanks for the info. Thought the table name after the UPDATE needed to be the full table name and not the alias. That makes sense now. BOL isn't exactly clear on that in their description of UPDATE.

    -- Kit

  • Kit G (1/18/2010)


    Chris Morris-439714 (1/15/2010)


    Hello

    The UPDATE and the SET apply to the same table.

    Ideally the first table listed after FROM is also the target of the update.

    In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET

    Ah. Thanks for the info. Thought the table name after the UPDATE needed to be the full table name and not the alias. That makes sense now. BOL isn't exactly clear on that in their description of UPDATE.

    No worries Kit. Other far wiser than I figured this out and backed it up with testing long ago.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

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