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

T-SQL
1
2
3
4
5
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
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

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier “DB2.dbo.Table1.Column1” could not be bound.

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

T-SQL
1
2
3
4
5
6
7
8
9
10
use tempdb
go
 
create table BlaTest(id int)
insert BlaTest values(1)
go
 
create table BlaTest2(id int)
insert BlaTest2 values(1)
go
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.

T-SQL
1
2
3
4
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
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

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier “tempdb.dbo.BlaTest2.id” could not be bound.

So what can be done?

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

T-SQL
1
2
3
4
update b
set b.id =a.id
from tempdb.dbo.BlaTest b
JOIN tempdb.dbo.BlaTest2 a on b.id =a.id
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.

T-SQL
1
2
3
4
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
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