Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the New York ColdFusion User Group (Sep. 2008) with:

MySQL: The Multi-part Identifier "u.id" Could Not Be Bound

Posted by Ben Nadel
Tags: SQL

I had never seen this error before, so I thought I would just take a second to post it in case anyone tries to Google it. Luckily, my query was extremely small and so the error was obvious from a quick review of the SQL; but, in a large query this might not be readily apparent. Here's the kind of query I was running in MySQL:

  • UPDATE
  • [user]
  • SET
  • is_active = 1
  • WHERE
  • u.id = 4

Can you see what the problem is? My WHERE clause uses an aliased table name, but my UPDATE clause did not alias the table. I am so used to using aliased tables that when I saw the error, it didn't even occur to me that the aliasing itself was causing the issue. Once I removed the alias "u.", the query worked fine.

Again, a really minor note, but I thought it might help someone.



Reader Comments

I've done this quite a few times. Its why I've trained myself to always use the alias, even when working with a single table.

Reply to this Comment

@Jon,

Agreed; I try to use the alias all the time. I just like it for some reason - I think it adds readability to the query (although that may just be a purely emotional response).

Reply to this Comment

Ah this helped me a lot, mine query was big one and I did aliased the table but after reading this when I checked back the query, there was a typo in alias. :-) So lots of time saved.

Reply to this Comment

I have meet the same question,but I don't think the origin error is the alias but the alias the way use it ,I recommened the way you use it like this:
UPDATE u
SET u.is_active = 1
from users as u
WHERE u.id = 4

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.