Using Alias in UPDATE SQL Statement

_{Friday, May 18, 2012 1:30 AM | 1 vote}

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:49 AM ✅Answered | 4 votes}

I thought it should use the alias in Update Statement.

update aset name = b.namefrom @tbl1 a, @tbl2 bwhere 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/

_{Friday, May 18, 2012 1:38 AM}

you should use the later one.

_{Friday, May 18, 2012 1:39 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:42 AM | 1 vote}

I would recommend following format

update aset a.name = b.namefrom @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:46 AM}

Try this
update @tbl1
set name = b.name
from @tbl2 b
where id = b.id;

Many Thanks & Best Regards, Hua Min