none
Using Alias in UPDATE SQL Statement

    Question

  • Hi,

    While checking a stored procedure, I found an Update statement that I think it will somehow fail to update the correct row. I have been doing a test to prove that it's not correct but the update statement is updating correctly. I just need someone to verify that the Update Statement is Okay and it will not fail at one point in time.

    SQL Server that we are using is 2008

    declare @tbl1 table (id int, name varchar(20))
    declare @tbl2 table (id int, name varchar(20))
    insert into @tbl1 select 1, null
    insert into @tbl1 select 2, null
    insert into @tbl1 select 3, null
    insert into @tbl2 select 1, 'JOHN'
    insert into @tbl2 select 2, 'ARMI'
    insert into @tbl2 select 3, 'TAN'

    The Update Statement in SP is something like this and yet when I did the test, it is working fine and updating the correct records.

    update @tbl1
    set name = b.name
    from @tbl1 a, @tbl2 b
    where a.id = b.id

    I thought it should use the alias in Update Statement.

    update a
    set name = b.name
    from @tbl1 a, @tbl2 b
    where a.id = b.id

    Thanks,

    Jon

    Friday, May 18, 2012 1:30 AM

Answers

  • I thought it should use the alias in Update Statement.

    update a
    set name = b.name
    from @tbl1 a, @tbl2 b
    where a.id = b.id

    The alias is needed only to avoid ambiguity.  From the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms177523.aspx):

    "If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias."

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by shadow-fire Friday, May 18, 2012 1:58 AM
    Friday, May 18, 2012 1:49 AM

All replies

  • you should use the later one.
    Friday, May 18, 2012 1:38 AM
  • both are same.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Friday, May 18, 2012 1:39 AM
  • I would recommend following format

    update a
    set a.name = b.name
    from @tbl1 a
        inner join @tbl2 b on a.id = b.id

    you can use other joining type as well such as left outer join. each row in the table being updated will be updated only once if any duplications are generated while joining.

    Regards

    John Huang, MVP-SQL, MCM-SQL, http://www.sqlnotes.info

    Friday, May 18, 2012 1:42 AM
  • Try this
    update @tbl1
    set name = b.name
    from @tbl2 b
    where id = b.id;

    Many Thanks & Best Regards, Hua Min


    Friday, May 18, 2012 1:46 AM
  • I thought it should use the alias in Update Statement.

    update a
    set name = b.name
    from @tbl1 a, @tbl2 b
    where a.id = b.id

    The alias is needed only to avoid ambiguity.  From the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms177523.aspx):

    "If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias."

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by shadow-fire Friday, May 18, 2012 1:58 AM
    Friday, May 18, 2012 1:49 AM