MySQL : When stored procedure parameter name is the same as table column name

Question

Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write

UPDATE customers SET Name = Name;

this is incorrect and I see 2 other ways:

UPDATE customers SET Name = `Name`;
UPDATE customers SET customers.Name = Name;

First one works, but I didn't find in documentation that I can wrap parameters inside ` characters. Or did I miss it in the documentation (link is appreciated in this case).

What other ways are there and what is the standard way for such a case? Renaming input parameter is not good for me (because I have automatic object-relational mapping if you know what I mean).

UPDATE:

So, there is a link about backticks (http://dev.mysql.com/doc/refman/5.0/en/identifiers.html) but it's not explained deep enough how to use them (how to use them with parameters and column names).

And there is a very strange thing (at least for me): You can use backticks either way:

UPDATE customers SET Name = `Name`;
//or
UPDATE customers SET `Name` = Name;
//or even
UPDATE customers SET `Name` = `Name`;

and they all work absolutely the same way.

Don't you think this is strange? Is this strange behavior explained somewhere?

Solution 1

Simplest way to distinguished between your parameter and column (if both name is same) is to add table name in your column name.

UPDATE customers SET customers.Name = Name;

Even you can also add database prefix like

UPDATE yourdb.customers SET yourdb.customers.Name = Name;

By adding database name you can perform action on more than 1 database from single store procedure.

Solution 2

I think that your first example is actually backwards. If you're trying to set the "Name" column to the "Name" input parameter, I believe it should be:

UPDATE customers SET `Name` = Name;

And for the second example, you can set table aliases the same way that you do in all other statements:

UPDATE customers AS c SET c.Name = Name;

Comments

[AaA]

That is a way of doing it, I know this answer is older than me. seems @ is not a valid prefix for procedure parameters, is there any other valid character that I can add instead? well, other than $, I don't want to mix those names with my php variables either.

[Garr Godfrey]

backticks only help if dealing with keywords. Name and ` Name ` are the same thing, so Name = ` Name ` will always be true