One of the best ways to improve your skills is by helping other people in forums and newsgroups. I was doing just that tonight and I stumbled on this piece of code here: http://stackoverflow.com/questions/3622685/transfer-column-data-from-one-database-to-another

update [DB1].[dbo].[Table1]
set [DB1].[dbo].[Table1].[Column1] = [DB2].[dbo].[Table1].[Column1]
from [DB1].[dbo].[Table1] db1Alias, [DB2].[dbo].[Table1] db2Alias
where db1Alias.TeamId = db2Alias.TeamId
and db1Alias.IndividualId = db2Alias.IndividualId

Can you tell what is wrong with the code? If you try to run that you will get the following error

The problem is that aliases are defined for the tables but not used in the column part.

Let’s take a closer look with some code that you can actually run. First create these two tables

use tempdb
go

create table BlaTest(id int)
insert BlaTest values(1)
go

create table BlaTest2(id int)
insert BlaTest2 values(1)
go

Now when you try to run this piece of code, which is the same as the code at the beginning of the post except for the object names, you will get an error.

update tempdb.dbo.BlaTest
set tempdb.dbo.BlaTest.id =tempdb.dbo.BlaTest2.id
from tempdb.dbo.BlaTest b
JOIN tempdb.dbo.BlaTest2 a on b.id =a.id

Here is the error

So what can be done?

Here is my preferred way of running this query, use the table aliases in the update and the columns

update b
set b.id =a.id
from tempdb.dbo.BlaTest b
JOIN tempdb.dbo.BlaTest2 a on b.id =a.id

But you can also write the query like this by using the alias only for the column that is not being updated.

update tempdb.dbo.BlaTest
set tempdb.dbo.BlaTest.id =a.id
from tempdb.dbo.BlaTest b
JOIN tempdb.dbo.BlaTest2 a on b.id =a.id

The important thing to remember is that you have to use the alias in the table that you are not updating, to be safe just use the alias all over, that way you don’t have to think whether to use the alias or not.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum