CODEWITHSUNDEEP
sqlsql-server-2008
DenaliHardtail
DenaliHardtail

Reputation: 28336

What is wrong with this use of the SQL alias (AS)?

I'm getting an error message when running the follwoing SQL statement. It's a pretty basic statement and I've got to be overlooking something REALLY simple.

If I remove the alias, the statement executes just fine. Add the alias back in and I get the error.

The message in SSMS:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "T1.titleId" could not be bound.

The SQL statement:

UPDATE People
SET T1.titleId = T2.ID 
FROM people as T1 
INNER JOIN titles as T2
ON T1.title = T2.Title

Update 1 - The comma is not part of the statement and I'm still getting the error.

Upvotes: 0

Views: 122

Answers (6)

HLGEM
HLGEM

Reputation: 96600

UPDATE t1 
SET titleId = T2.ID  
FROM people as T1  
INNER JOIN people as T2 
ON T1.titleId = T2.titleId

You do not alias the field on the left side of the SET

Upvotes: 0

Chandu
Chandu

Reputation: 82933

EDIT: Changed People to T1 You have a comma after T1 that is creating the problem.

Change the statement to:

UPDATE T1 -- People 
  SET T1.titleId = T2.ID  
 FROM people as T1 --Removed the comma that was here
 INNER JOIN titles as T2 
   ON T1.title = T2.Title

Upvotes: 3

Keith.Abramo
Keith.Abramo

Reputation: 6965

Try this:

UPDATE t1
SET T1.titleId = T2.ID 
FROM people as T1 
INNER JOIN people as T2
ON T1.titleId = T2.titleId

It can not find T1.titleId because you are updating "Person" and sql can only see the Person tables that you aliased as T1 and T2. So you would want to update T1 in your case. Also you had an extra comma in your inner join.

Upvotes: 2

Brian Driscoll
Brian Driscoll

Reputation: 19635

You have some syntax issues in your UPDATE. It should be:

UPDATE T1
SET titleId = T2.ID
FROM people as T1
INNER JOIN titles as T2
ON T1.title = T2.Title

Upvotes: 5

JNK
JNK

Reputation: 65187

You have two issues.

1 - The comma. I'm guessing you used the deprecated implicit JOIN syntax at some point.

2 - You should also use the alias on the UPDATE line when you have a JOIN. You are SETing a field in t1 but UPDATEing people which is inconsistent.

Try:

UPDATE t1
SET T1.titleId = T2.ID 
FROM people as T1
INNER JOIN titles as T2
ON T1.title = T2.Title

Upvotes: 4

rajah9
rajah9

Reputation: 12339

I think the comma after as T1, is superfluous. Please try removing it.

Upvotes: 2

Related Questions